asked on

CONVERT ROWS TO COLUMNS IN SQL 2008

Hi,

1.table
ID NAME
-------------
1 JOHN
2. MARY

2. table
ID DOCUMENT DATE
--------------------------------------
1 WORD 15.10.10
1 EXCEL 15.10.10
1 PP 15.10.10
1 n... dd.mm.yy
2 EXCEL 13.10.10
2 WORD 14.10.10
2 n... dd.mm.yy

I need table like this
ID NAME DOC_1, DATE_1 DOC_2, DATE_2 DOC_3, DATE_3 DOC_n DATE_n
1 JOHN WORD 15.10.10 EXCEL 15.10.10 PP 15.10.10 n... dd.mm.yy
2 MARY EXCEL 13.10.10 WORD 14.10.10

- I have to put columns in the rows, and number of columns depends on number of rows in second table.
- I don't know how many documents and ID could have

Thx in advance

Walter Ritzel

You have two solutions here:
1 - Use a stored procedure to create this table;
2 - Try to use a pivot table.

Can you be more specific on what you are trying to accomplish here?

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Well, that is an interesting problem, and PIVOT can certainly do that for you, just takes a bit of setting up, and, defining what the columns really will be...

Check out below for an "intro" and then work on the second part as outlined in the article : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

create table #table_1 (id int, [name] varchar(20))
create table #table_2 (id int, [document] varchar(20), [date] datetime)

insert #table_1 values (1,'JOHN')
insert #table_1 values (2,'MARY')

insert #table_2 values (1,'WORD','20101015')
insert #table_2 values (1,'EXCEL','20101015')
insert #table_2 values (1,'PP','20101015')
insert #table_2 values (2,'WORD','20101015')
insert #table_2 values (2,'EXCEL','20101015')
insert #table_2 values (2,'PP','20101015')


-- so now we have some test data, lets play with some T-SQL queries

Select * from
( select t1.id, t1.name, 'Document_' + convert(varchar,row_number() over (partition by t1.id order by t2.[document],t2.[date])) as Code_type, t2.[document] as Code_Value from #table_1 T1 inner join #table_2 T2 on t1.id = t2.id
  union all
  select t1.id, t1.name, 'Date_' + convert(varchar,row_number() over (partition by t1.id order by t2.[document],t2.[date])) as Code_Type, convert(varchar,t2.[date],103) as Code_Value from #table_1 T1 inner join #table_2 T2 on t1.id = t2.id ) srce
PIVOT
( max(code_value) for  code_type in ([document_1],[date_1],[document_2],[date_2],[document_3],[date_3]) )pvt

-- so that is the basic layout, but we need something to help define the different columns. But first lets also consider

Select * from
( select t1.id, t1.name, t2.[document], t2.[date] from #table_1 T1 inner join #table_2 T2 on t1.id = t2.id ) srce
PIVOT
( max([date]) for  [document] in ([Excel],[Word],[PP]) )pvt

-- either which way we need to ascertain the column names to us in the "IN" clause of the pivot


-- see http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

-- now lets get that column list happening.... For that we do need some dynamic SQL (or create the procedure per the article)

declare @columns varchar(100)

select @columns = isnull(@columns+',','') 
                + '[Document_' + convert(varchar,row_number() over (order by t2.[document])) 
                + '],[Date_' + convert(varchar,row_number() over (order by t2.[document])) + ']'
from #table_1 T1 inner join #table_2 T2 on t1.id = t2.id group by t2.document

exec('
Select * from
( select t1.id, t1.name, ''Document_'' + convert(varchar,row_number() over (partition by t1.id order by t2.[document])) as Code_type, t2.[document] as Code_Value from #table_1 T1 inner join #table_2 T2 on t1.id = t2.id
  union all
  select t1.id, t1.name, ''Date_'' + convert(varchar,row_number() over (partition by t1.id order by t2.[document])) as Code_Type, convert(varchar,t2.[date],103) as Code_Value from #table_1 T1 inner join #table_2 T2 on t1.id = t2.id ) srce
PIVOT
( max(code_value) for  code_type in ('+@columns+') )pvt')

Open in new window