question

DavidPhillips avatar image
DavidPhillips asked

Joined table crosstab in sql server

Hi I have a database where my data is in many tables that I need to join together. One table has a couple of hundred names in a field called taskid. I am going to join all tables on taskid replacing the taskid with the corresponding name. However I also need to display the information in rows where the names are the column headers. Table1 taskid |f1 1 |100 2 |101 3 |102 Table2 taskid | name 1 | name1 2 | name2 3 | name3 Output Name1 | name2 | name3 100 | 101 | 102 Thanks
pivotcrosstab
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
Something like this should work, though column names should be hard coded. create table #tabl1 (id int, f1 int) insert into #tabl1 values (1,100),(2,101),(3,102) create table #tabl2 (id int, nam varchar(10)) insert into #tabl2 values (1,'name1'),(2,'name2'),(3,'name3') select t1.id,t1.f1,t2.nam into #mediate_table from #tabl1 t1 join #tabl2 t2 on t1.id = t2.id select * from #mediate_table select id,name1,name2,name3 from (select id,f1,nam from #mediate_table ) as MT PIVOT ( SUM(f1) FOR NAM IN (name1,name2,name3) ) as pivot_mt
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.