Convert the result set from row wise to column wise

• s.kumaraguru

  SSC Journeyman

  Points: 95

  More actions

  October 24, 2007 at 6:53 am

  #176593

  Hi Friends,

  I want to Convert the result set from column wise to row wise. For Example, if i execute on select query i have the following output.

  SupplierKey Value

  ----------- -----

  BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 70

  BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 80

  BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 90

  I just want to chage the result set like this.

  SupplierKey Value1 value2 value3

  ----------- ------ ------ ------

  BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 70 80 90

  can any one give some solution for this?

  Regards,

  Kumar

• SQLMAIN

  SSC Eights!

  Points: 936

  More actions

  October 24, 2007 at 9:35 am

  #745019

  This is using a recent post on PIVOT SQL technique.

  --Creating a table with your type of data

  create table TT

  (

  SUPKEY varchar(100),

  value tinyint

  )

  insert into TT values ('BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 70)

  insert into TT values ('BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 80)

  insert into TT values ('BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 90)

  insert into TT values ('AF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 70)

  insert into TT values ('AF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 80)

  insert into TT values ('AF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 90)

  insert into TT values ('XF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 70)

  --select * from tt

  --Create another temp table with UR data and a KEY col

  drop table #temp

  SELECT SUPKEY, VALUE, CAST(0 AS INT) AS KEYVAL

  INTO #temp

  FROM tt

  --Index - so we can put correct values for KEY coL

  CREATE CLUSTERED INDEX Composite ON #temp (SUPKEY, VALUE)

  DECLARE @PrevSUPKEY varchar(100)

  SET @PrevSUPKEY = 0

  DECLARE @keycnt INT

  --Create running count in the KEY Column

  UPDATE #temp

  SET @keycnt = KEYVAL = CASE WHEN SUPKEY = @PrevSUPKEY THEN @keycnt+1 ELSE 1 END,

  @PrevSUPKEY = SUPKEY

  FROM #temp WITH (INDEX(Composite),TABLOCKX)

  --Now create a dynamix SQL to produce the reqd output

  DECLARE @SQL0 VARCHAR(8000)

  DECLARE @SQL1 VARCHAR(8000)

  DECLARE @SQL2 VARCHAR(8000)

  SELECT @SQL0 = 'SELECT SUPKEY,'

  SELECT @SQL1 = ISNULL(@SQL1+',','')+CHAR(10)

  + 'MAX(CASE WHEN KEYVAL = ' + CAST(d.KEYVAL AS VARCHAR(10)) + ' '

  + 'THEN VALUE ELSE '''' END) AS VALUE' + CAST(d.KEYVAL AS VARCHAR(10))

  FROM (SELECT DISTINCT KEYVAL FROM #temp) d

  ORDER BY d.KEYVAL

  SELECT @SQL2 = CHAR(10)+'FROM #temp GROUP BY SUPKEY'

  --Execute the dynamic SQL

  --PRINT @SQL0+@SQL1+@SQL2

  exec (@SQL0+@SQL1+@SQL2)

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  October 28, 2007 at 10:09 pm

  #746079

  thanks a lot ....

• VaiydeyanathanVS

  Hall of Fame

  Points: 3053

  More actions

  October 28, 2007 at 11:26 pm

  #746083

  Try this....

  msdn2.microsoft.com/en-us/library/aa216173(sql.80).aspx