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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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