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
hrvica5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
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?
cyberkiwiCommented:
CrossTab proc fixed and enhanced from
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
CREATE procedure CrossTab (@Select varchar(max),
    @PivotCol varchar(max),
    @Summaries varchar(max),
    @GroupBy varchar(max),
    @OtherCols varchar(max) = Null)
AS
set nocount on
set ansi_warnings off

declare @sql varchar(max)
declare @Vals varchar(max);
set @Vals = '';
set @OtherCols= isNull(', ' + @OtherCols,'')
create table #temp  (PV varchar(max))
set @sql = 'select distinct convert(varchar(max),' + @PivotCol + ') as PV FROM (' + @Select + ') A'

insert into #temp
exec (@sql)
select @Vals = @Vals + ', ' +
    replace(replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' +
            PV +  ''' THEN '),')', ' END)'), ']', PV + ']')
from #Temp
order by PV
drop table #Temp
print @vals

set @sql = 'select ' + @GroupBy + @OtherCols + @Vals +
       ' from (' + @Select + ') A GROUP BY ' + @GroupBy
exec ( @sql )

set nocount off
set ansi_warnings on
GO

---- first create the function above, then run the query below

exec CrossTab
'select t1.id, t1.name, t2.document, t2.date,
 rn=row_number() over (partition by t1.id order by t2.date)
from table1 t1
inner join table2 t2 on t1.id=t2.id',
'rn',
'Max(document)[Doc_],Max(date)[Date_]',
'id,name',
null

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic Advisor, Page EditorCommented:
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 : http://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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.