Convert the result set from row wise to column wise

  • 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

  • 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)

  • thanks a lot ....

  • Try this....

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply