Problems with a Pivot - 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 2008 Forums

Transact-SQL (2008)

Problems with a Pivot

Author

Topic

ifthenelsenull
Starting Member

3 Posts

Posted - 2011-12-17 : 13:27:26

I'm trying to pivot data for the first time and it's giving me some problems. I inserted data into a temp table and then queried that table for the pivot. It returned all the data however the pivot created two rows instead of one. I'm hoping someone can show me what I did wrong.

The procedure is as follows:

SELECT 'TOP_VALUE' AS New_VALUE,
[N_no],
[ Type1 ],
[ Type8 ],
[ Type7 ],
[ Type6 ],
[ Type5 ],
[ Type4 ],
[ Type3 ],
[ Type2 ],
[ Stg1 ],
[ Stg2 ],
[ Stg3 ],
[ Stg4 ],
[ Stg5 ],
[ Stg6 ],
[ Stg7 ],
[ Stg8 ],
[A_BradenScore ],
[D_N_SknRisk ]
FROM
(SELECT *
FROM #tbla0data
) AS SourceTable

PIVOT
(
MIN(VALUE)
FOR findingabbr IN (
[N_no],
[ Type1 ],
[ Type8 ],
[ Type7 ],
[ Type6 ],
[ Type5 ],
[ Type4 ],
[ Type3 ],
[ Type2 ],
[ Stg1 ],
[ Stg2 ],
[ Stg3 ],
[ Stg4 ],
[ Stg5 ],
[ Stg6 ],
[ Stg7 ],
[ Stg8 ],
BradenScore ],
[SknRisk ])
) AS PivotTable;

The data from the temptable #tbla0data is here

patientAccountID Formusage CollectedDT creationtime Findingabbr Value vcreationtime AssessmentID
1234 ASMT_ADMISSION 47:00.0 47:00.0 N_No Yes 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Stg1 Stage 2 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Stg2 Stage 2 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Stg3 Stage 4 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Stg4 Unstageable 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Type1 Pressure Ulcer 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Type2 Pressure Ulcer 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Type3 Pressure Ulcer 52:41.7 3931516
1234 ASMT_ADMISSION 47:00.0 47:00.0 Type4 Pressure Ulcer 52:41.7 3931516

The end result of the pivot is here:

New_VALUE N_no Type1 Type8 Type7 Type6 Type5 Type4 Type3 Type2 Stg1 Stg2 Stg3 Stg4 Stg5 Stg6 Stg7 Stg8 BradenScore SknRisk
TOP_VALUE NULL Pressure Ulcer NULL NULL NULL NULL Pressure Ulcer NULL NULL Stage 2 NULL Stage 4 Unstageable NULL NULL NULL NULL NULL NULL
TOP_VALUE Yes NULL NULL NULL NULL NULL NULL Pressure Ulcer Pressure Ulcer NULL Stage 2 NULL NULL NULL NULL NULL NULL NULL NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 00:38:06

[code]
SELECT 'TOP_VALUE' AS New_VALUE,
[N_no],
[ Type1 ],
[ Type8 ],
[ Type7 ],
[ Type6 ],
[ Type5 ],
[ Type4 ],
[ Type3 ],
[ Type2 ],
[ Stg1 ],
[ Stg2 ],
[ Stg3 ],
[ Stg4 ],
[ Stg5 ],
[ Stg6 ],
[ Stg7 ],
[ Stg8 ],
[A_BradenScore ],
[D_N_SknRisk ]
FROM
(SELECT DISTINCT findingabbr,VALUE
FROM #tbla0data
) AS SourceTable

PIVOT
(
MIN(VALUE)
FOR findingabbr IN (
[N_no],
[ Type1 ],
[ Type8 ],
[ Type7 ],
[ Type6 ],
[ Type5 ],
[ Type4 ],
[ Type3 ],
[ Type2 ],
[ Stg1 ],
[ Stg2 ],
[ Stg3 ],
[ Stg4 ],
[ Stg5 ],
[ Stg6 ],
[ Stg7 ],
[ Stg8 ],
BradenScore ],
[SknRisk ])
) AS PivotTable;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ifthenelsenull
Starting Member

3 Posts

Posted - 2011-12-18 : 22:23:43

Thank you very much. That worked perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 23:44:38

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources