Another Complex Pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Another Complex Pivot table

Author

Topic

BigSexy
Starting Member

2 Posts

Posted - 2008-05-12 : 11:48:00

How go I pivot this data? Field MNGR should became columns and GATE used as data for pivot.

CustomerID CaseID DeptID MNGR GATE
1 278 58 SMITH JAX
1 278 58 GREEN ATL
1 278 58 PITTMAN LBC
1 278 20 COLE MCH
1 278 20 MANN ATL

It should look like this:

CustomerID CaseID DeptID SMITH GREEN PITTMAN COLE MANN
1 278 58 JAX ATL LBC null null
1 278 20 COLE MCH null MCH ATL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-12 : 11:51:54

You will need dynamic sql for this, since the number of columns can vary.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 11:58:57

[code]SELECT CustomerID,CaseID,
MAX(CASE WHEN MNGR='SMITH' THEN GATE ELSE NULL END) AS SMITH,
MAX(CASE WHEN MNGR='GREEN' THEN GATE ELSE NULL END) AS GREEN,
MAX(CASE WHEN MNGR='PITTMAN' THEN GATE ELSE NULL END) AS PITTMAN,
MAX(CASE WHEN MNGR='COLE' THEN GATE ELSE NULL END) AS COLE,
MAX(CASE WHEN MNGR='MANN' THEN GATE ELSE NULL END) AS MANN
FROM Table
GROUP BY CustomerID,CaseID
[/code]

However if manger names are not static you need to use PIVOT with dynamic clause

something like

[code]DECLARE @ManagerList varchar(8000)
SELECT @ManagerList = LEFT(ml.MgrLst,LEN(ml.MgrLst)-1)
FROM
(SELECT DISTINCT MNGR + ',' AS [text()]
FROM Table
FOR XML PATH(''))ml(MgrLst)

SELECT @SQL=
'SELECT * FROM
(SELECT *
FROM Table) m
PIVOT (MAX(GATE) FOR MNGR IN (['+ REPLACE(@ManagerList,',','],[') + ']))p'

EXEC(@SQL)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources