Need a select query to get the output as shown below.?

Question

0.00/5 (No votes)

See more:

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

Posted 12-Jan-17 0:25am

Raj 0203

Updated 13-Jan-17 9:08am

Add a Solution

Comments

Maciej Los 12-Jan-17 7:47am

To display data in that format, you'll need reporting tools. You can't create 2 row headers.

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-01-12T02:07:00

Raj 0203[^] wrote in the comment to the solution #1:
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...

SQL

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

Moses Lebotsa · Answer 2 · 2017-01-12T00:37:00

Solution 1

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

Posted 12-Jan-17 0:37am

Moses Lebotsa

Comments

Raj 0203 12-Jan-17 7:15am

This is the way i tried, but i didn't got the exact output as required. Please Re-Check my Output, as it was having two Header Rows ,
1.| Loc | Jan-16 | Mar-16 | May-16 | and
2.| |A |B |C |A |B |C |A |B |C |
The second row is like a sub heading for the first header column.

Can you please help.

Patrice T · Answer 3 · 2017-01-13T09:08:00

Solution 4

Learn about SQL pivot.

SQL Functions[^]
Using PIVOT and UNPIVOT[^]
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]

Posted 13-Jan-17 9:08am

Patrice T

Need a select query to get the output as shown below.?

3 solutions

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0

Need a select query to get the output as shown below.?

3 solutions

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us