如何将列数据转换为SQL Server 2008中的行 [英] How to transpose colum data into rows in SQL server 2008
问题描述
您好b $ b
我写了以下查询以获取特定用户的总数来制作生产报告
我的Sql查询是:
SELECT distinct(资源),work_area,范围,
CASE
WHEN(范围='DataEntry'和work_area ='编码')那么image_count
WHEN(scope ='DataEntry'和work_area ='QC')那么image_count / 4
WHEN(范围='技术')然后总和(转换(int,[持续时间] * 6.25))
ELSE''
结束'总计'
FROM DATEPART(MM,dat_e)='07'和DATEPART(YEAR,dat_e)='2016'的工作组BY资源,范围,image_count,持续时间,work_area;
我得到的结果如下:
Hi
I wrote the below query to get total count of particular user to produce the production report
My Sql Query is:
SELECT distinct(resources), work_area, scope,
CASE
WHEN (scope = 'DataEntry' and work_area='Coding') THEN image_count
WHEN (scope = 'DataEntry' and work_area='QC') THEN image_count/4
WHEN (scope='Technical' ) THEN sum(convert(int,[duration]*6.25))
ELSE ''
END 'Total'
FROM work where DATEPART(MM,dat_e)='07' and DATEPART(YEAR,dat_e)='2016' Group BY resources, scope, image_count, duration, work_area;
and I am getting results like below:
resources work_area scope Total
John Other Technical 65
John QA Technical 150
Mary Other Technical 40
Rita Coding DataEntry 800
Rita Coding DataEntry 900
Rita Coding DataEntry 950
Rita Coding DataEntry 700
Rita QC DataEntry 800
Rita Coding DataEntry 850
Rita QC DataEntry 700
sharo Coding DataEntry 900
sharo Other Technical 200
sharo Coding DataEntry 800
我希望输出如下
and I want the output like below
resources Technical DataEntry Total
John 215 215
Mary 40 40
Rita 5700 5700
Sharo 200 1700 1900
任何人都可以帮我解决这个问题问题
我尝试了什么:
我试图转置将数据分成行,但我无法获得所需的结果,任何人都可以帮助我。
Can anyone please help me to resolve this issue
What I have tried:
I have tried to transpose the data into rows but i am not able to get the desired result can anyone plase help me on this.
推荐答案
选项1:使用Pivo t建议为0x01AA。
选项2:对同一个表使用JOINS以您希望的方式对数据进行切片。
根据您提供的内容,下面应该工作。
Option 1: Use Pivot as 0x01AA suggested.
Option 2: Use JOINS against the same table to slice the data the way you want it.
Based on what you have provided, the below should work.
SELECT
a.Resources,
COALESCE(SUM(CONVERT(INT, d.duration*6.25)), 0) AS Technical,
COALESCE(SUM(b.image_count), 0) + (COALESCE(SUM(c.image_Count), 0)/4) AS DataEntry,
COALESCE(SUM(CONVERT(INT, d.duration*6.25)), 0) + COALESCE(SUM(b.image_count), 0) +
(COALESCE(SUM(c.image_Count), 0)/4) AS Total
FROM
work a
LEFT JOIN work d ON a.resources = d.resources AND d.scope = 'Technical' AND YEAR(d.dat_e) = '2016' AND MONTH(d.dat_e) = '7'
LEFT JOIN work b ON a.resources = b.resources AND b.scope = 'DataEntry' AND b.work_area = 'Coding' AND YEAR(b.dat_e) = '2016' AND MONTH(b.dat_e) = '7'
LEFT JOIN work c ON a.resources = c.resources AND c.scope = 'Technical' AND c._work_area = 'QC' AND YEAR(c.dat_e) = '2016' AND MONTH(c.dat_e) = '7'
GROUP BY
a.Resources
亲切的问候
Kind Regards
看一下这个链接,希望这能帮到你:
sql server - 在Sql中转换列和行的简单方法? - 堆栈溢出 [ ^ ]
Take a look at this link hope this will help you out:
sql server - Simple way to transpose columns and rows in Sql? - Stack Overflow[^]
这篇关于如何将列数据转换为SQL Server 2008中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!