CODEWITHSUNDEEP
asp.netsqlsql-server
user2451954
user2451954

Reputation: 45

How to pivot table in SQL

Lab Test Name    Source Collected Date    Results
Urea             6/4/2013 12:00:00 AM           5
Uric Acid        6/4/2013 12:00:00 AM          10
Cholesterol      6/3/2013 12:00:00 AM          25

I have a datatable with above values.
I need to pivot it to following structure: 

                      Urea  Uric Acid   Cholesterol
6/4/2013 12:00:00 AM     5         10             -
6/3/2013 12:00:00 AM     -          -            25

Upvotes: 2

Views: 134

Answers (2)

Icarus
Icarus

Reputation: 63966

If you look at the answer linked by Mikael, you'll realize that you will need to build the columns for your pivot statement dynamically since the PIVOT syntax doesn't allow a subselect within the FOR clause. So essentially you need to do something like this:

DECLARE
@cols AS NVARCHAR(MAX),
@y    AS INT,
@sql  AS NVARCHAR(MAX);

-- Construct the column list for the IN clause
-- e.g., [Urea],[Uric Acid],[Cholesterol]
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT (LabTestName) AS y FROM YourTable) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

You can now build your PIVOT statement as so:

set @SQL = N'

SELECT   SourceCollectedDate,'+@cols+N' 
FROM    YourTable
PIVOT (
SUM(results) FOR LabTestName IN ( '+@cols+N')
) AS PivotTable
ORDER BY SourceCollectedDate desc
'

And execute it:

EXEC sp_executesql @sql

Which will produce:

SourceCollectedDate     Urea    Uric Acid   Cholesterol
2013-06-04 00:00:00.000 5       10          NULL
2013-06-03 00:00:00.000 NULL    NULL        25

Just note that my example has YourTable as the table name. You need to replace that with your actual table name.

SQL Fiddle (Based off of what Chad created)

Upvotes: 3

Chad
Chad

Reputation: 7507

Here's a solution that doesn't require pivot or dynamic SQL. The tradeoff is that you need to specify each possible Lab Test Name in your query.

SELECT [Source Collected Date],
  MAX(CASE WHEN [Lab Test Name] = 'Urea'
      THEN Results ELSE NULL END) AS Urea,
  MAX(CASE WHEN [Lab Test Name] = 'Uric Acid'
      THEN Results ELSE NULL END) AS [Uric Acid],
  MAX(CASE WHEN [Lab Test Name] = 'Cholesterol'
      THEN Results ELSE NULL END) AS Cholesterol
FROM Table1
GROUP BY [Source Collected Date]

See it working here.

Upvotes: 1

Related Questions