如何将列数据转换为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屋！