SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Row to column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GilbertoH
Starting Member

Panama
4 Posts

Posted - 11/26/2012 :  18:54:07  Show Profile  Reply with Quote
I have a table like

NAME DATA
name1 A
name2 B
name1 C
name3 A
name2 D
name1 A

Those two columns, I need to group them on a view like:

NAME Data1 Data2 Data3 Data4 Data5
name1 A C A Null Null
name2 B D Null Null Null
name3 A Null Null Null Null



--
Regards, GilbertoH.

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/26/2012 :  23:37:36  Show Profile  Reply with Quote

DECLARE @tab TABLE(NAME varchaR(10), DATA varchar(3))
INSERT INTO @tab VALUES('name1', 'A'),('name2', 'B'),('name1', 'C'),('name3', 'A'),('name2', 'D'),('name1', 'A')


SELECT distinct t.NAME, STUFF( (SELECT ',' + s.DATA FROM @tab s WHERE s.NAME = t.NAME FOR XML PATH('')), 1, 1, '') as data
INTO #test
FROM @tab t

DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[DATA'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT 
    @select='
        select p.*
        from (
        select 
            name,substring(data, start+2, endPos-Start-2) as token,
            ''DATA''+cast(row_number() over(partition by name order by start) as varchar(10)) as n
        from (
            select 
                name, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        name, '','' + data +'','' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test


--
Chandu
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 11/27/2012 :  07:12:18  Show Profile  Reply with Quote
Hi,

Please refer to the following link for more information on Pivot and Unpivot that might help to solve questions like you posted.

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000