February 12, 2009 at 7:53 am
Guys,
We have a database that has a lot of coded values, some in seperate tables, so I need a way to PIVOT them to get the outoput as one row. In the below example I use PIVOT to transpose one code table. But to transpose code values from the second code table, I am using PIVOT on PIVOT.
Wondering if there is a better way... for multiple PIVOTs
thanks,
_UB
Example:
-- DROP TABLE dbo.pat_table
CREATE TABLE dbo.pat_table (ID INT IDENTITY(1,1), Name VARCHAR(50), Admin_UID INT)
GO
-- DROP TABLE dbo.udf_table
CREATE TABLE dbo.udf_table (ID INT IDENTITY(1,1), Admin_UID INT, udf_code VARCHAR(50), udf_value VARCHAR(50))
GO
-- DROP TABLE dbo.udf_table2
CREATE TABLE dbo.udf_table2 ( ID INT IDENTITY(1,1), Admin_UID INT, udf_code VARCHAR(50), udf_value VARCHAR(50))
GO
INSERT INTO dbo.pat_table
SELECT 'Bob Smith' , 1234 UNION ALL
SELECT 'Cathy Jones', 5678
GO
INSERT INTO dbo.udf_table
SELECT 1234, 'Religion', 'Catholic' UNION ALL
SELECT 1234, 'Hair Color', 'Brown' UNION ALL
SELECT 1234, 'Gender', 'Male' UNION ALL
SELECT 5678, 'Religion', 'Jewish' UNION ALL
SELECT 5678, 'Hair Color', 'Blond' UNION ALL
SELECT 5678, 'Gender', 'Female'
GO
INSERT INTO dbo.udf_table2
SELECT 1, 'SS', '123456789' UNION ALL
SELECT 1, 'DL', '1235878' UNION ALL
SELECT 1, 'MI', '1235468' UNION ALL
SELECT 2, 'PI', '897546' UNION ALL
SELECT 2, 'ANON', '54687452' UNION ALL
SELECT 2, 'SS', '4544'
GO
SELECT * FROM dbo.pat_table
SELECT * FROM dbo.udf_table
SELECT * FROM dbo.udf_table2
--
-- PIVOT one coded table
--
SELECT id, Name, Admin_UID, [Religion], [Hair Color], [Gender]
FROM (SELECT P.id, P.Name, P.Admin_UID, u.udf_code, u.udf_value
FROM dbo.pat_table AS p
INNER JOIN dbo.udf_table AS u
ON P.Admin_uid = u.Admin_uid) AS Source
PIVOT
(
MAX(udf_value)
FOR udf_code IN ([Religion], [Hair Color], [Gender])
) AS PVT
--
-- PIVOT on PIVOT: multiple PIVOT
--
;WITH PIV1 AS
( SELECT id, Name, Admin_UID, [Religion], [Hair Color], [Gender]
FROM (SELECT P.id, P.Name, P.Admin_UID, u.udf_code, u.udf_value
FROM dbo.pat_table AS p
INNER JOIN dbo.udf_table AS u
ON P.Admin_uid = u.Admin_uid) AS Source
PIVOT
(
MAX(udf_value)
FOR udf_code IN ([Religion], [Hair Color], [Gender])
) AS PVT
)
SELECT *
FROM ( SELECT PIV1.ID, PIV1.Name, PIV1.Admin_UID, PIV1.[Religion], PIV1.[Hair Color], PIV1.[Gender], u.udf_code, u.udf_value
FROM PIV1
INNER JOIN dbo.udf_table2 AS u
ON PIV1.id = u.Admin_uid) AS Source2
PIVOT
(
MAX(udf_value)
FOR udf_code IN ([SS], [PI], [ANON], [DL], [MI])
) PVT2
February 12, 2009 at 8:36 am
Here is an updated version with ONLY one PIVOT and it should also perform better than the one you have
by approximately 30% (though I am not sure about it, because I've not tested it)...
;WITH PivotCTE
AS
(
SELECT 'U1' AS KeyType, Admin_UID, udf_code, udf_value
FROM dbo.udf_table
UNION ALL
SELECT 'U2' AS KeyType, Admin_UID, udf_code, udf_value
FROM dbo.udf_table2
)
SELECT ID, [Name], Admin_UID, [Religion], [Hair Color], [Gender], [SS], [PI], [ANON], [DL], [MI]
FROM (
SELECT p.ID, p.Name, p.Admin_UID, c.udf_code, c.udf_value
FROM dbo.pat_table p
INNER JOIN PivotCTE c ON ( p.Admin_UID = c.Admin_UID AND c.KeyType = 'U1' )
OR ( p.ID = c.Admin_UID AND c.KeyType = 'U2' )
) C
PIVOT
(
MAX( udf_value ) FOR udf_code IN ( [Religion], [Hair Color], [Gender], [SS], [PI], [ANON], [DL], [MI] )
) PVT
--Ramesh
February 12, 2009 at 9:51 am
Ramesh,
Thanks for the solution, it works great.
Now I'll try to build it using Dynamic SQL, as there could be 10 - 15 code tables involved and we don't always know the number of codes from each table.
thanks again,
_Uday
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply