Thursday, 22 January 2015
SQL Pivot and Stuff
Query to pivot and stuff information to show all values
DECLARE @DateToUse DATETIME = GETDATE()
CREATE TABLE #TestData
(
DivisionName VARCHAR(150)
,DepartmentName VARCHAR(150)
,PolicyRef VARCHAR(150)
,InceptionDate DATETIME
,ExpiryDate DATETIME
,[Group] VARCHAR(100)
,UserName VARCHAR(150) collate latin1_general_ci_as
,ProportionPercentage NUMERIC(3,0)
)
INSERT #TestData
( DivisionName
, DepartmentName
, PolicyRef
, InceptionDate
, ExpiryDate
, [Group]
, UserName
, ProportionPercentage
)
VALUES
( 'Division1' -- DivisionName - varchar(150)
, 'Department1' -- DepartmentName - varchar(150)
, 'Policy1' -- PolicyRef - varchar(150)
, @DateToUse-20 -- InceptionDate - datetime
, @DateToUse+344 -- ExpiryDate - datetime
, 'Account Executive' -- Group - varchar(100)
, 'User1' -- UserName - varchar(150)
, 50 -- ProportionPercentage - numeric
)
INSERT #TestData
( DivisionName
, DepartmentName
, PolicyRef
, InceptionDate
, ExpiryDate
, [Group]
, UserName
, ProportionPercentage
)
VALUES
( 'Division1' -- DivisionName - varchar(150)
, 'Department1' -- DepartmentName - varchar(150)
, 'Policy1' -- PolicyRef - varchar(150)
, @DateToUse-20 -- InceptionDate - datetime
, @DateToUse+344 -- ExpiryDate - datetime
, 'Account Executive' -- Group - varchar(100)
, 'User2' -- UserName - varchar(150)
, 50 -- ProportionPercentage - numeric
)
INSERT #TestData
( DivisionName
, DepartmentName
, PolicyRef
, InceptionDate
, ExpiryDate
, [Group]
, UserName
, ProportionPercentage
)
VALUES
( 'Division1' -- DivisionName - varchar(150)
, 'Department1' -- DepartmentName - varchar(150)
, 'Policy1' -- PolicyRef - varchar(150)
, @DateToUse-20 -- InceptionDate - datetime
, @DateToUse+344 -- ExpiryDate - datetime
, 'Account Handler' -- Group - varchar(100)
, 'User3' -- UserName - varchar(150)
, 0 -- ProportionPercentage - numeric
)
SELECT
DivisionName
, DepartmentName
, PolicyRef
, InceptionDate
, ExpiryDate
, [Group]
, UserName
, ProportionPercentage = CAST(ProportionPercentage AS VARCHAR(10)) collate latin1_general_ci_as + '%'
INTO
#TestRaw
FROM
#TestData
WHERE
@DateToUse BETWEEN InceptionDate AND ExpiryDate
SELECT
DivisionName
, DepartmentName
, PolicyRef
, InceptionDate
, ExpiryDate
, [Group]
, STUFF(( SELECT
',' + ST.UserName + ' ' + ST.ProportionPercentage
FROM
#TestRaw ST
WHERE
ST.PolicyRef = T.PolicyRef
AND ST.[Group] = T.[Group]
FOR
XML PATH('')
) , 1 , 1 , '') AS UserName
INTO
#TEST
FROM
#TestRaw T
SELECT
*
INTO
#Test2
FROM
#TEST PIVOT ( MAX(UserName) FOR [Group] IN ( [Account Executive] ,
[Account Handler] ,
[Account Requestor] ,
[Accounts Manager] ,
[Brokers] , [Claims] ,
[Technical] ) ) AS PivotName
SELECT DISTINCT
T.DivisionName
, T.DepartmentName
, T.PolicyRef
, T.InceptionDate
, T.ExpiryDate
INTO
#Test3
FROM
#TEST T
SELECT
T3.DivisionName
, T3.DepartmentName
, T3.PolicyRef
, T3.InceptionDate
, T3.ExpiryDate
, T2.[Account Executive]
, T2.[Account Handler]
, T2.[Account Requestor]
, T2.[Accounts Manager]
, T2.Brokers
, T2.Claims
, T2.Technical
FROM
#Test3 T3
LEFT JOIN #Test2 T2 ON T2.PolicyRef = T3.PolicyRef
DROP TABLE #TestRaw
DROP TABLE #TEST
DROP TABLE #Test2
DROP TABLE #Test3
DROP TABLE #TestData
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment