需要选择查询才能获得如下所示的输出。 [英] Need a select query to get the output as shown below.?
问题描述
我有一个SQL表(示例),如下所示,
| Loc |日期| ID | STS |
-----------------------------
| Hyd | 15-01-2016 | 1 | A |
| Vjd | 16-01-2016 | 2 | B |
| Viz | 15-01-2016 | 3 | C |
| Hyd | 15-03-2016 | 4 | A |
| Vjd | 15-03-2016 | 5 | B |
| Viz | 15-03-2016 | 6 | C |
| Hyd | 15-03-2016 | 4 | A |
| Vjd | 15-05-2016 | 5 | B |
| Viz | 15-05-2016 | 6 | C |
我需要输出,如
| Loc | 1月16日| 3月16日| 5月16日|
| | A | B | C | A | B | C | A | B | C |
-------------------------------------
| Hyd | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 |
| Vjd | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 |
| Viz | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 |
任何人都可以帮助我。 。
提前致谢。
我尝试了什么:
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
- 获取不同的值PIVOT列
SELECT @ ColumnName = ISNULL(@ColumnName +',','')
+ QUOTENAME([Date])
FROM(SELECT DISTINCT [Date] FROM [Sample] )AS [Date] order by [Date]
--print @ColumnName
- 使用动态
预备PIVOT查询SET @DynamicPivotQuery =
N' SELECT [Loc],'+ @ColumnName +'
FROM [Sample]
PIVOT(SUM(Count(*))
FOR [Date] IN('+ @ColumnName +')) AS PVTTable'
- 执行动态数据透视查询
EXEC sp_executesql @DynamicPivotQuery
请重新检查我的输出,因为它有两个标题行,
正如我在问题的评论中提到的,你不能仅使用T-SQL实现这一点。您需要报告工具,例如 ReportViewer [ ^ ] , CrystalReports [ ^ ]等。
仅使用T-SQL,您可以使用单个标题实现结果集,即:
| Loc | sts | 1月16日| 3月16日| 5月16日|
-------------------------------------
| Hyd | A | 1 | 0 | 0 |
| Hyd | B | 2 | 0 | 0 |
| Vjd | A | 0 | 1 | 0 |
| Vjd | B | 0 | 1 | 0 |
| Viz | A | 0 | 0 | 1 |
| Viz | B | 0 | 0 | 1 |
或
| Loc |日期| A | B | C |
-----------------------------
| Hyd | 1月16日| 1 | 0 | 0 |
| Hyd | 3月16日| 0 | 0 | 0 |
| Hyd | 5月16日| 1 | 2 | 1 |
或混合内容如:
| Loc | Jan-16_A | Jan-16_B | Jan-16_C | Mar-16_A | Mar-16_B | Mar_16_C | ...等等......
------------------------------------- ------------------------------------------
| Hyd | 1 | 0 | 0 | 2 | 0 | 0 | ...
| Vjd | ..
| Viz | ...
但是,由于性能问题,我不建议使用此类视图。
根据您的意愿...
< span class =code-keyword> SET DATEFORMAT dmy;我的SQL Server需要 -
创建 表 #tmp
(
Loc VARCHAR ( 5 ),
[日期] 日期,
Id INT ,
Sts VARCHAR ( 5 )
);
INSERT INTO #tmp(Loc,[日期],Id,Sts)
VALUES (' Hyd',' 15-01-2016',' 1',' A'),
(' Vjd' ,' 16-01-2016', 2 ,' B'),
(' Viz',' 15-01-2016', 3 ,' C'),
(' Hyd',' 15-03-2016', 4 ,' A'),
(' Vjd',' 15 -03-2016', 5 ,' B'),
(' Viz',' 15-03-2016', 6 ,' C'),
('' Hyd',' 15-03-2016', 4 ,' A'),
(< span class =code-string>' Vjd',' 15-05-2016', 5 ,' B'),
(' Viz',' 15-05-2016', 6 ,' C')
DECLARE @ ColumnName AS NVARCHAR (MAX)
SELECT @ ColumnName = ISNULL ( @ ColumnName + ' ,',' ') + QUOTENAME(标题)
FROM (
SELECT t3.PoT + ' _' + t3.Sts AS 标题
FROM (
SELECT DISTINCT CONVERT ( VARCHAR ( 3 ),DATENAME(M,t1。[日期]))+ ' - ' + CONVERT ( VARCHAR ( 2 ),DATENAME(Y,t1。[日期])) AS PoT ,t2.Sts
FROM #tmp AS t1,(
SELECT DISTINCT Sts AS Sts
FROM #tmp
) AS t2
) AS t3
) AS 组合
- SELECT @ColumnName
DECLARE @ DynamicPivotQuery AS NVARCHAR (MAX)= N ' '
SET @ DynamicPivotQuery = N < span class =code-string>' SELECT Loc,' + @ColumnName + '
FROM(
SELECT Loc,CONVERT(VARCHAR) (3),DATENAME(M,[Date]))+'' - ''+ CONVERT(VARCHAR(2),DATENAME(Y,[Date]))+''_''+ Sts AS PoT,1 AS ToT
FROM #tmp
)AS DT
PIVOT(SUM(ToT)FOR PoT IN(' + @ ColumnName + ' ))AS PT'
EXECUTE SP_EXECUTESQL @ DynamicPivotQuery
DROP TABLE #tmp
结果:
Loc Jan-15_A Jan-15_B Jan-15_C Jan-16_A Jan-16_B Jan-16_C Mar-75_A Mar-75_B Mar-75_C May-13_A May-13_B May-13_C
Hyd 1 NULL NULL NULL NULL NULL 2 NULL NULL NULL NULL NULL
Viz NULL NULL 1 NULL NULL NULL NULL NULL 1 NULL NULL 1
Vjd NULL NULL NULL NULL 1 NULL N ULL 1 NULL NULL 1 NULL
嗨Raj。您可以使用数据透视表将列结果转换为列标题。请在以下链接。
请注意,您需要根据结果返回日期和月份。
我希望这会对你有所帮助。欢呼
了解SQL pivot。
SQL函数 [ ^ ]
使用PIVOT和UNPIVOT [ ^ ]
Sql Server中的PIVOT和UNPIVOT | SqlHints.com [ ^ ]
I Have a SQL Table(Sample) as shown below,
| Loc | Date | Id| Sts| ----------------------------- | Hyd | 15-01-2016 | 1 | A | | Vjd | 16-01-2016 | 2 | B | | Viz | 15-01-2016 | 3 | C | | Hyd | 15-03-2016 | 4 | A | | Vjd | 15-03-2016 | 5 | B | | Viz | 15-03-2016 | 6 | C | | Hyd | 15-03-2016 | 4 | A | | Vjd | 15-05-2016 | 5 | B | | Viz | 15-05-2016 | 6 | C | And i need output like, | Loc | Jan-16 | Mar-16 | May-16 | | |A |B |C |A |B |C |A |B |C | ------------------------------------- | Hyd |1 |0 |0 |2 |0 |0 |0 |0 |0 | | Vjd |0 |1 |0 |0 |1 |0 |0 |1 |0 | | Viz |0 |0 |1 |0 |0 |1 |0 |0 |1 |
Can anyone help me out please..
Thanks in Advance.
What I have tried:
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME([Date]) FROM (SELECT DISTINCT [Date] FROM [Sample]) AS [Date] order by [Date] --print @ColumnName --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery = N'SELECT [Loc], ' + @ColumnName + ' FROM [Sample] PIVOT(SUM(Count(*)) FOR [Date] IN (' + @ColumnName + ')) AS PVTTable' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery
Please Re-Check my Output, as it was having two Header Rows ,
As i mentioned in the comment to the question, you can't achieve that using T-SQL only. You need reporting tools, such as ReportViewer[^], CrystalReports[^], etc.
Using T-SQL only, you can achieve a result set with single header, i.e.:
| Loc | sts | Jan-16 | Mar-16 | May-16 | ------------------------------------- | Hyd | A | 1 | 0 | 0 | | Hyd | B | 2 | 0 | 0 | | Vjd | A | 0 | 1 | 0 | | Vjd | B | 0 | 1 | 0 | | Viz | A | 0 | 0 | 1 | | Viz | B | 0 | 0 | 1 |
or
| Loc | Date | A | B | C | ----------------------------- | Hyd | Jan-16 | 1 | 0 | 0 | | Hyd | Mar-16 | 0 | 0 | 0 | | Hyd | May-16 | 1 | 2 | 1 |
or a mixed content like:
| Loc | Jan-16_A | Jan-16_B | Jan-16_C | Mar-16_A | Mar-16_B | Mar_16_C | ... and so on... ------------------------------------------------------------------------------- | Hyd | 1 | 0 | 0 | 2 | 0 | 0 | ... | Vjd | .. | Viz | ...
but, i do not recommend to use such of view due to performance issues.
[EDIT]
As per your wish...
SET DATEFORMAT dmy; --needed at my sql server CREATE TABLE #tmp ( Loc VARCHAR(5), [Date] Date, Id INT, Sts VARCHAR(5) ); INSERT INTO #tmp (Loc, [Date],Id, Sts) VALUES ('Hyd', '15-01-2016', '1', 'A'), ('Vjd', '16-01-2016', 2, 'B'), ('Viz', '15-01-2016', 3, 'C'), ('Hyd', '15-03-2016', 4, 'A'), ('Vjd', '15-03-2016', 5, 'B'), ('Viz', '15-03-2016', 6, 'C'), ('Hyd', '15-03-2016', 4, 'A'), ('Vjd', '15-05-2016', 5, 'B'), ('Viz', '15-05-2016', 6, 'C') DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName = ISNULL(@ColumnName + ', ','') + QUOTENAME(Header) FROM ( SELECT t3.PoT + '_' + t3.Sts AS Header FROM ( SELECT DISTINCT CONVERT(VARCHAR(3), DATENAME(M, t1.[Date])) + '-' + CONVERT(VARCHAR(2), DATENAME(Y, t1.[Date])) AS PoT, t2.Sts FROM #tmp AS t1, ( SELECT DISTINCT Sts AS Sts FROM #tmp ) AS t2 ) AS t3 ) AS combined --SELECT @ColumnName DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) = N'' SET @DynamicPivotQuery = N' SELECT Loc, ' + @ColumnName + ' FROM ( SELECT Loc, CONVERT(VARCHAR(3), DATENAME(M, [Date])) + ''-'' + CONVERT(VARCHAR(2), DATENAME(Y, [Date])) + ''_'' + Sts AS PoT, 1 AS ToT FROM #tmp ) AS DT PIVOT(SUM(ToT) FOR PoT IN (' + @ColumnName + ')) AS PT' EXECUTE SP_EXECUTESQL @DynamicPivotQuery DROP TABLE #tmp
Result:
Loc Jan-15_A Jan-15_B Jan-15_C Jan-16_A Jan-16_B Jan-16_C Mar-75_A Mar-75_B Mar-75_C May-13_A May-13_B May-13_C Hyd 1 NULL NULL NULL NULL NULL 2 NULL NULL NULL NULL NULL Viz NULL NULL 1 NULL NULL NULL NULL NULL 1 NULL NULL 1 Vjd NULL NULL NULL NULL 1 NULL NULL 1 NULL NULL 1 NULL
Hi Raj. You can use pivot tables to turn column results into column headers. Please find a clear, quick, and short example at the following link.
Please note you will need to return your dates and month-year as per your results.
I hope this helps you. cheers
Learn about SQL pivot.
SQL Functions[^]
Using PIVOT and UNPIVOT[^]
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]
这篇关于需要选择查询才能获得如下所示的输出。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!