~

https://stackoverflow.com/questions/15133240/how-to-create-query-in-sql-to-pivot-data

TABLE: PRODUCT
slno  product
1          x    
2          y
3          z

TABLE: DETAIL
product    detail
x          good
y          bad
z          worse
x          bad

I need to get output as

TABLE
X      Y       Z
good   bad     worse
bad

===========================================================

You can use an aggregate function with a CASE expression:

select 
  max(casewhen product ='x'then detail end) x,
  max(casewhen product ='y'then detail end) y,
  max(casewhen product ='z'then detail end) z
from(select p.product, d.detail,
    row_number()over(partitionby p.product orderby p.slno) rn
  from product p
  innerjoin detail d
    on p.product = d.product
) src
groupby rn

See SQL Fiddle with Demo

You can use the PIVOT function:

select x, y, z
from(select p.product, d.detail,
    row_number()over(partitionby p.product orderby p.slno) rn
  from product p
  innerjoin detail d
    on p.product = d.product
) src
pivot(
  max(detail)for product in(x, y, z)) piv

See SQL Fiddle with Demo.

If you have an unknown number of values (products in this case) to turn into columns, then you will want to use dynamic SQL:

DECLARE@cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)select@cols = STUFF((SELECTdistinct','+ QUOTENAME(product)from product
            FORXML PATH(''), TYPE
            ).value('.','NVARCHAR(MAX)'),1,1,'')set@query ='SELECT '+@cols +' from 
             (
                select p.product, d.detail,
                  row_number() over(partition by p.product order by p.slno) rn
                from product p
                inner join detail d
                  on p.product = d.product
            ) x
            pivot 
            (
                max(detail)
                for product in ('+@cols +')
            ) p 'execute(@query)

See SQL Fiddle with Demo

The result of all of the queries is:

|    X |      Y |      Z |--------------------------| good |    bad |  worse ||  bad |(null)|(null)|