February 16, 2006 at 3:24 am
Hi Folks,
This maybe a very simple question, we have just upgraded to SQL server 2005, and we have a issue that the new PIVOT function might be able to help us with. Lets say you have a record set that looks like this:
Red
Green
Blue
Is there a way that we can use the PIVOT function to simple switch this to:
Red,Green,Blue
Basically we need to convert rows to columns in one clean swoop. this may seem trivial, but I have not figured out how to do this yet, and at the moment we use a cursor in a function to do this and its very slow indeed!
Any help would be much appreciated!
David.
February 16, 2006 at 4:55 am
You can not get in single query. but you can use bunch of statement
copy following code in Query analyser
DECLARE @S1 AS VARCHAR(400)
SET @S1=''
SELECT @S1 = @S1+', ' + [field1] FROM
PRINT SUBSTRING(@S1,2,LEN(@S1))
February 16, 2006 at 5:10 am
PIVOT allows you to manipulate the data to give it in effectively another format.
So assuming other columns of itemid and shape
--A table something like this
CREATE TABLE objects (itemid int, volume int, shape nvarchar(10), colour nvarchar(10))
--and arbitrary values
INSERT INTO dbo.objects SELECT 1, 10, 'square', 'red';
INSERT INTO dbo.objects SELECT 2, 25, 'triangle','green';
INSERT INTO dbo.objects SELECT 3, 15, 'square', 'blue';
--You can run this
SELECT shape, [red], [green], [blue] FROM
(SELECT itemid, shape, colour FROM dbo.objects)
objects
PIVOT (COUNT (itemid) FOR colour IN ([red], [green], [blue])) tblPivot
--To get colour based columns, and shape based rows
February 16, 2006 at 5:13 am
Surely if you just want a list of values SELECT DISTINCT would be better.
February 16, 2006 at 6:02 am
Hi,
How would SELECT DISTINCT work?
Cheers.
February 16, 2006 at 7:02 am
Continuing from my example table:
SELECT DISTINCT colour FROM dbo.objects
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply