How to Pivot table in sql
-
Hi,
I have created a table with CTE as:
;with cte([Patent Professional],[Novelty Searches]
,[Pending Landscape]
,[Pending Product Clearance]
,[Other Pending Searches])
as
(select [ShortName],( select COUNT([CategoryID]) FROM [dbo].[IPD_SearchSheetData] a
WHERE a.[PersonResponsibleID]=b.[UserID] and [CategoryID]=6) ,( select COUNT([CategoryID]) FROM [dbo].[IPD_SearchSheetData] a
WHERE a.[PersonResponsibleID]=b.[UserID] and [CategoryID]=1),( select COUNT([CategoryID]) FROM [dbo].[IPD_SearchSheetData] a
WHERE a.[PersonResponsibleID]=b.[UserID] and [CategoryID]=3),( select COUNT([CategoryID]) FROM [dbo].[IPD_SearchSheetData] a
WHERE a.[PersonResponsibleID]=b.[UserID] and [CategoryID] in (2 , 4 , 5 , 7))
From [IPDDashboardDev].[dbo].[IPD_SiemensUsers] b )
SELECT * FROM CTEMy output is:
But i want the output as:
Note: Patent Professionals column of the cte is dynamic.
Please help me ASAP. -
How about this:
--Create Sample Table
CREATE TABLE #Sample
(Patent varchar(3),
Novelty int,
Landscape int,
Clearance int,
Searching int);
GO--Insert Sample Data
INSERT INTO #Sample
VALUES
('KRR',0,0,0,3),
('PRD',0,0,0,3),
('EJ',0,0,0,3),
('KT',0,0,0,0),
('PV',0,0,0,0);
GO--Have a quick look at data in current form
SELECT *
FROM #Sample;
GO--Solution query, using UNPIVOT
SELECT Profession,
SUM(CASE WHEN Patent = 'KRR' THEN PatentValue ELSE 0 END) AS KRR,
SUM(CASE WHEN Patent = 'PRD' THEN PatentValue ELSE 0 END) AS PRD,
SUM(CASE WHEN Patent = 'EJ' THEN PatentValue ELSE 0 END) AS EJ,
SUM(CASE WHEN Patent = 'KT' THEN PatentValue ELSE 0 END) AS KT,
SUM(CASE WHEN Patent = 'PV' THEN PatentValue ELSE 0 END) AS PV
FROM
(SELECT S.Patent, S.Novelty, S.Landscape, S.Clearance, S.Searching
FROM #Sample S) p
UNPIVOT
(PatentValue FOR Profession IN (Novelty,Landscape,Clearance,Searching)) AS UP
GROUP BY Profession;GO
--Clean up
DROP TABLE #Sample;
GOThom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Also, that query you have at the start is... well, honestly, awful :). I doubt you really need to actually do what I've done above and could easily achieve this without a pivot, however, I don't have any consumable test data. Either way, you should really get rid of those subqueries and use a proper JOIN clause. Something like this should work:
SELECT SU.ShortName AS [Patent Professional],
SUM(CASE WHEN SSD.CategoryID = 6 THEN 1 ELSE 0 END) AS [Novelty Searches],
SUM(CASE WHEN SSD.CategoryID = 1 THEN 1 ELSE 0 END) AS [Pending Landscape],
SUM(CASE WHEN SSD.CategoryID = 3 THEN 1 ELSE 0 END) AS [Pending Product Clearance],
SUM(CASE WHEN SSD.CategoryID IN (2,4,5,7) THEN 1 ELSE 0 END) AS [Other Pending Searches]
FROM IPDDashboardDev.dbo.IPD_SiemensUsers SU
LEFT JOIN IPDDashboardDev.dbo.IPD_SearchSheetData SSD ON SU.UserID = SSD.PersonResponsibleID;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, April 26, 2017 2:15 AM
A different version with a more flexible unpivot method (explained in here)
SELECT Profession,
SUM(CASE WHEN Patent = 'KRR' THEN PatentValue ELSE 0 END) AS KRR,
SUM(CASE WHEN Patent = 'PRD' THEN PatentValue ELSE 0 END) AS PRD,
SUM(CASE WHEN Patent = 'EJ' THEN PatentValue ELSE 0 END) AS EJ,
SUM(CASE WHEN Patent = 'KT' THEN PatentValue ELSE 0 END) AS KT,
SUM(CASE WHEN Patent = 'PV' THEN PatentValue ELSE 0 END) AS PV
FROM #Sample S
CROSS APPLY (VALUES ('Novelty ', Novelty ),
('Landscape', Landscape),
('Clearance', Clearance),
('Searching', Searching))up(Profession, PatentValue)
GROUP BY Profession; -
Better do things right from the beginning. Don't pivot to later unpivot and pivot again. Do a single pivot.
Here's an example:
SELECT CASE WHEN SSD.[CategoryID] = 6 THEN 'Novelty Searches'
WHEN SSD.[CategoryID] = 1 THEN 'Pending Landscape'
WHEN SSD.[CategoryID] = 3 THEN 'Pending Product Clearance'
WHEN SSD.[CategoryID] IN(2,4,5,7) THEN 'Other Pending Searches'
ELSE 'Others' END,
COUNT( CASE WHEN SU.[ShortName] = 'KRR' THEN SU.[ShortName] END) AS [KRR],
COUNT( CASE WHEN SU.[ShortName] = 'PRD' THEN SU.[ShortName] END) AS [PRD],
COUNT( CASE WHEN SU.[ShortName] = 'EJ' THEN SU.[ShortName] END) AS [EJ]
FROM [IPDDashboardDev].[dbo].[IPD_SiemensUsers] SU
JOIN [dbo].[IPD_SearchSheetData] SSD WHERE SSD.[PersonResponsibleID]=SU.[UserID]
GROUP BY CASE WHEN SSD.[CategoryID] = 6 THEN 'Novelty Searches'
WHEN SSD.[CategoryID] = 1 THEN 'Pending Landscape'
WHEN SSD.[CategoryID] = 3 THEN 'Pending Product Clearance'
WHEN SSD.[CategoryID] IN(2,4,5,7) THEN 'Other Pending Searches'
ELSE 'Others' END;
To make it dynamic, read the following article: http://www.sqlservercentral.com/articles/Crosstab/65048/ -
Once again my favorite TSQL key word (CASE) is part of the solution!!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - Luis Cazares - Wednesday, April 26, 2017 7:33 AM
This is more what i was getting at, thanks Luis. Didn't want to go the whole hog and make assumptions on the OP's data though. 😀
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you so much:)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply