asked on

Rows to Columns

Hi.
I have this sample tables
Table A
Field1 | FK Table2
Name1      | 10
Name2      | 20
Name2      | 30
Name3      | 10
Name2      | 20

Table B
Field1 | Field 2
10      | TTT
10      | XXX
20      | TTT
10      | ZZZ
30      | TTT

And i want to obtain a table with table b rows as columns
something like this

Table

Name1      | TTT | XXX | ZZZ
Name2      | TTT | |
Name2      | TTT | |
Name3      | TTT | XXX | ZZZ
Name2      | AAA | |

rajvja

Hi

Good article here

http://www.sqlservercentral.com/articles/T-SQL/63681/

ashokdudhade

Please refer below article. I find it helpful.
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

waltersnowslinarnold

Use the following URL's: It might help

http://msdn.microsoft.com/en-us/library/ms177410.aspx

http://sqlservercodebook.blogspot.com/2008/04/covert-row-to-column-in-sql-server-2005.html

Ravindra_Gautam

Dear ,

if " FK Table2" of tableA is a refrence key of TableB than you can simply use the below query

Select a.*,b.* from TableA as a join TableB as b on a.[FK Table2]=b.Field1

it will surly get the desired result. if the above condition is tru.

thanks

Ravindra_Gautam

Dear,

Sorry ignore my privious post, wrongly intercepeted your requirement, will update you shortly with the right query for the required result set.

thanks

ralmada

A simple solution will be to create a single comma delimited column like this
select distinct
a.Field1,
stuff((select ',' + Field2 from TableB where Field2 = a.FKTable2 for xml path('')), 1, 1, '') as Fields2
from TableA a
If you still prefer to have separate columns for each FKTable2 then can do something like this:

Declare @strSQL varchar(max)        
Declare @cols varchar(2000)        
      
SELECT  @cols = STUFF((SELECT DISTINCT         
                                '],[' + cast(Field1 as varchar)
                        From TableB   
                        ORDER BY 1        
                        FOR XML PATH('')        
                      ), 1, 2, '') + ']'        
         
         
set @strSQL = 'select a.Field1, ' + @cols +       
' from (       
        Select a.Field1, a.FKTable2, b.Field2, from TableA a
	inner join TableB b on a.FKTable2 = b.Field1
) o       
pivot (max(Field2) for FkTable2 in (' + @cols + ')) as p'     
     
exec(@strSQL)

Open in new window

TvMpt

ASKER

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

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

Start Free Trial