如何将行转换为sql中的列。 [英] How to transpose rows into columns in sql.

问题描述

您好b $ b

我想显示所选月份和年份的日期报告。并在我的下面的查询中它工作。但问题是我想将日期Rows转换为列。





请有人帮我解决这个问题。



我尝试过：

 SELECT 
 a.Resources，a.dat_e，
 
（COALESCE（SUM（CONVERT（INT，d.duration * 6.25）），0））+（COALESCE（SUM（CONVERT（INT，b.image_count）），0））+（COALESCE（SUM（CONVERT（ INT，e.image_count）），0）/ 4）+（COALESCE（SUM（CONVERT（INT，f.doc_count *（cast（f.fieldscount as int）））），0））
 AS Total 
 FROM 
工作
 LEFT JOIN工作d on a.resources = d.resources和a.Workid_PK = d.workid_PK AND（d.scope ='Technical Services'和d.work_area ='其他'）和a.dat_e = d.dat_e和年（d.dat_e）='2018'和（月（d.dat_e）='05'）
 LEFT JOIN工作b ON a.resources = b。资源和a.Workid_PK = b.workid_PK AND（b.scope ='LDD'和b.Work_area ='编码'）和a.dat_e = b.dat_e AND YEAR（b.dat_e）='2018'和（月（ b.dat_e）='05'）
 LEFT JOIN工作e on a.resources = e.resources and a.Workid_PK = e.workid_PK AND（e.scope ='LDD'和e.Work_area ='QC'）和a.dat_e = e.dat_e和YEAR（e.dat_e）='2018'AND（月（e。 dat_e）='05'）
 LEFT JOIN工作f ON a.resources = f.resources和a.Workid_PK = f.workid_PK AND（f.scope ='Coding'AND f.Work_area ='Coding'）和a.dat_e = f.dat_e AND YEAR（f.dat_e）='2018'AND（MONTH（f.dat_e）='05'）
 where（YEAR（a.dat_e）='2018'和月（ a.dat_e）='05'）
 
 GROUP BY 
 a.Resources，a.dat_e 

输出为：

 John 5/1/2018 0 
 Ram 5/14/2018 2000 
 John 5/2/2018 0 
 Ram 5/3/2018 0 
 Philip 5/10/2018 8484 
 Prince 5/6/2018 0 
 John 5/4/2018 0 
 Ram 5/5/2018 0 
 John 5/6/2018 0 
 Ram 5/7/2018 0 
 Philip 5/8/2018 0 
 Prince 5/9/2018 0 

但我想转置日期栏作为标题

任何人请帮我解决这个。

 Res 5/1/2018 5/2/2018 5/3/2018 5 /二千零十八分之四2018年5月5日2018年5月6日2018年5月7日2018年5月8日2018年5月9日2018年5月10日2018年5月11日2018年5月12日2018年5月13日5 / 14/2018 
 Ram 2000 
 John 500 
 Philip 8484 

解决方案

见这里：使用PIVOT和UNPIVOT | Microsoft Docs [ ^ ]，此处：在SQL查询中使用Pivot的简单方法 [ ^ ]

那么，根据OriginalGriff提供的文档，你可以实现通过使用静态版本的pivot（您必须为每个日期键入列的名称）：

  SELECT 资源，[5/1/2018]，[5/2/2018]，[5/3/2018]，... [5/13/2018]，[5/14/2018] 
  FROM （
   -   您的查询结果如何在这里！ 
） AS  Src 
 PIVOT（总计< span class =code-keyword> FOR  dat_e  IN （[5/1/2018]，[5/2/2018]，[5 / 3/2018]，... [5/13/2018]，[5/14/2018]）） AS  PVT 

对于动态版本，其中日期的列名称是在运行时创建的，请参阅：

在SQL Server中创建动态PIVOT查询的脚本 [< a href =https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/target =_ blanktitle =New Window> ^ ]

SQL脚本：在SQL Server中使用动态数据透视表将行转换为列 - TechNet文章 - 美国（英语） - TechNet Wiki [ ^ ] br />
Sql Server中的动态PIVOT | SqlHints.com [ ^ ]

SQL Server中的动态PIVOT查询 - SQLRelease [ ^ ]

SQL Server中的动态数据透视查询 [ ^ ]

Hi
I want to display datewise report of selected month and year. and in my below query it worked. But the problem is i want to transpose the date Rows into Columns.


Please anyone help me to resolve this.

What I have tried:

SELECT 
    a.Resources,a.dat_e,
     
    (COALESCE(SUM(CONVERT(INT, d.duration*6.25)), 0)) + (COALESCE(SUM(CONVERT(INT, b.image_count)), 0)) +(COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) + (COALESCE(SUM(CONVERT(INT, f.doc_count*(cast(f.fieldscount as int)))), 0)) 
     AS Total
FROM 
    work a
    LEFT JOIN work d ON a.resources = d.resources and a.Workid_PK=d.workid_PK AND (d.scope = 'Technical Services' and d.work_area='other') AND a.dat_e=d.dat_e AND YEAR(d.dat_e) = '2018' AND (MONTH(d.dat_e) ='05') 
    LEFT JOIN work b ON a.resources = b.resources and a.Workid_PK=b.workid_PK AND (b.scope = 'LDD' AND b.Work_area='Coding') and a.dat_e=b.dat_e AND YEAR(b.dat_e) = '2018' AND (MONTH(b.dat_e)='05') 
    LEFT JOIN work e ON a.resources = e.resources and a.Workid_PK=e.workid_PK AND (e.scope = 'LDD' AND e.Work_area='QC') and a.dat_e=e.dat_e and YEAR(e.dat_e) = '2018' AND (MONTH(e.dat_e) ='05') 
    LEFT JOIN work f ON a.resources = f.resources and a.Workid_PK=f.workid_PK AND (f.scope = 'Coding' AND f.Work_area='Coding') and a.dat_e=f.dat_e AND YEAR(f.dat_e) = '2018' AND (MONTH(f.dat_e) ='05') 
where (YEAR(a.dat_e) = '2018' AND MONTH(a.dat_e) ='05')
   
GROUP BY
    a.Resources, a.dat_e

Output is:

John	5/1/2018		0
Ram	5/14/2018	     2000
John	5/2/2018		0
Ram	5/3/2018		0
Philip	5/10/2018	     8484
Prince	5/6/2018		0
John	5/4/2018		0
Ram	5/5/2018		0
John	5/6/2018		0
Ram	5/7/2018		0
Philip	5/8/2018		0
Prince	5/9/2018		0

but i want to transpose the date column as header
anyone Please help me to resolve this.

Res	5/1/2018	5/2/2018	5/3/2018	5/4/2018	5/5/2018	5/6/2018	5/7/2018	5/8/2018	5/9/2018	5/10/2018	5/11/2018	5/12/2018	5/13/2018	5/14/2018
Ram														2000
John		500												
Philip										8484				

解决方案

See here: Using PIVOT and UNPIVOT | Microsoft Docs[^] and here: Simple Way To Use Pivot In SQL Query[^]

Well, based on the documentation provided by OriginalGriff, you can achieve that by using 'static' version of pivot (you have to type the names of columns for each date):

SELECT Resources, [5/1/2018],	[5/2/2018],	[5/3/2018], ... [5/13/2018], [5/14/2018]
FROM (
    --Your query goes here!
) AS Src
PIVOT(Total FOR dat_e IN([5/1/2018],	[5/2/2018],	[5/3/2018], ... [5/13/2018], [5/14/2018])) AS PVT

For 'dynamic' version, where the column-names for date are created in a run-time, please see:
Script to create dynamic PIVOT queries in SQL Server[^]
SQL Script: Convert Rows To Columns Using Dynamic Pivot In SQL Server - TechNet Articles - United States (English) - TechNet Wiki[^]
Dynamic PIVOT in Sql Server | SqlHints.com[^]
Dynamic PIVOT query in SQL Server - SQLRelease[^]
Dynamic Pivot Query in SQL Server[^]

这篇关于如何将行转换为sql中的列。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！