April 25, 2017 at 11:30 pm
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 CTE
My output is:
But i want the output as:
Note: Patent Professionals column of the cte is dynamic.
Please help me ASAP.
April 26, 2017 at 2:15 am
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;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2017 at 3:07 am
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
April 26, 2017 at 7:23 am
Thom A - Wednesday, April 26, 2017 2:15 AMHow 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;
GO
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;
April 26, 2017 at 7:33 am
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/
April 26, 2017 at 7:44 am
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
April 26, 2017 at 7:46 am
Luis Cazares - Wednesday, April 26, 2017 7:33 AMBetter 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/
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
April 30, 2017 at 2:19 am
Thank you so much:)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy