Pivot the rows to columns

Question

Post reply

Pivot the rows to columns

SQLServerMS

SSC Veteran

Points: 223
More actions
May 6, 2011 at 5:09 am

#250396

Hi,
I have data as below
name code guid
abc c.o.2 84B42B8B-E429-F122-8F3F-116167B22CF6
DEF c.o.2.1 73A31A7G-D362-S357-3D5G-4F4345F4FEI3
I want the output as below
sample c.o.2 c.o.2.1
sample 84B42B8B-E429-F122-8F3F-116167B22CF6 73A31A7G-D362-S357-3D5G-4F4345F4FEI3
could u help how i can do this
Have a nice day!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

I'm not sure where the 'sample' label is coming from, but you can use a pivot to get your results

declare @vs_string varchar(max) = 'create table #T (Name varchar(10), Code varchar(10), GuidField varchar(50))

declare @vs_sql varchar(max)

insert into #T

values(''abc'', ''c.o.2'', ''84B42B8B-E429-F122-8F3F-116167B22CF6''),

(''DEF'', ''c.o.2.1'', ''73A31A7G-D362-S357-3D5G-4F4345F4FEI3'')

DECLARE @Colslist VARCHAR(MAX)

DECLARE @Cols TABLE (Head VARCHAR(MAX))

INSERT @Cols (Head)

SELECT DISTINCT Code

FROM #T

SELECT @ColsList = COALESCE(@ColsList + '',['', ''['') + Head + '']''

FROM @Cols t

set @vs_sql = ''SELECT ''''Sample'''' as sample, *

FROM

(

SELECT code, GuidField

FROM #T

) t

PIVOT (max(GuidField) FOR Code IN ('' + @ColsList + '')) PVT''

exec (@vs_sql)'

exec (@vs_string)

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WayneS SSC Guru Points: 95460 More actions · Answer 2

Check out the link in my signature on cross-tab and pivot tables.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2