Pivoting Data - 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 2000 Forums

Transact-SQL (2000)

Pivoting Data

Author

Topic

boogybaz
Starting Member

8 Posts

Posted - 2007-01-18 : 04:57:36

Hi, hope you guys can help me out here, I have data which I want to pivot, but all of it is text. The data I have is as follows

Result ResultType ResultDesc ID
Yes BATCH DETAILS Labelled 1000102
1.0 BATCH DETAILS Weight 1000102
120 BATCH DETAILS Size 1000102
10 BATCH DETAILS Weight Each 1000102
1.0-1.5 DEFECTS Pressure Range 1000102
NO BATCH DETAILS Labelled 1000199
1.9 BATCH DETAILS Weight 1000199
140 BATCH DETAILS Size 1000199
12 BATCH DETAILS Weight Each 1000199
YES DEFECTS Other 1000199
1.2-1.5 DEFECTS Pressure Range 1000199

What I need to get out of that table is the data in the following

Labelled Weight Size Weight Each Pressure Range Other
YES 1.0 120 10 1.0-1.5 null
NO 1.9 140 12 1.2-1.5 YES

So far, been trying the following

Select ResultType,
		--MAX(case ResultDesc WHEN 'Labelled' THEN RESULT ELSE 0 END) AS 'Labelled',
		 MAX(CASE ResultDesc WHEN 'Weight' THEN RESULT ELSE 0 END) AS 'weight',
		 MAX(CASE ResultDesc WHEN 'Size' THEN RESULT ELSE 0 END) AS 'Size'
FROM Tablename 
Group By ResultType

but is throwing up errors converting the YES to a int field..

Help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 05:15:23

You can't mix integers and varchar with the same function.

Either try

SELECT		ResultType,
		MAX(CASE ResultDesc WHEN 'Labelled' THEN RESULT ELSE '0' END) AS 'Labelled',
		MAX(CASE ResultDesc WHEN 'Weight' THEN RESULT ELSE '0' END) AS 'weight',
		MAX(CASE ResultDesc WHEN 'Size' THEN RESULT ELSE '0' END) AS 'Size'
FROM		Tablename 
GROUP BY	ResultType

SELECT		ResultType,
		MAX(CASE ResultDesc WHEN 'Labelled' THEN LTRIM(STR(RESULT)) ELSE '0' END) AS 'Labelled',
		MAX(CASE ResultDesc WHEN 'Weight' THEN LTRIM(STR(RESULT)) ELSE '0' END) AS 'weight',
		MAX(CASE ResultDesc WHEN 'Size' THEN LTRIM(STR(RESULT)) ELSE '0' END) AS 'Size'
FROM		Tablename 
GROUP BY	ResultType

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources