Post reply

Converting rows into columns

  • September 25, 2011 at 10:16 am

    #243631

    Hello Friends,

    I need to join 2 tables and convert the rows in one table to columns in the result. I have attached the screenshot of the table output and expected output. Please let me know if it would be possible for us to derive the value in this format.

    Thanks

    Regards

  • September 25, 2011 at 2:31 pm

    #1386241

    Yes, it certainly is possible. Please see the following article for how to do it.

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

    I suspect that you could someday have more than 3 "skills" listed. For how to take care of that, please see Part 2 of the above article at the following URL.

    http://qa.sqlservercentral.com/articles/Crosstab/65048/

    If you'd like to see some tested code out of this, screen shots just don't help in the creation of test data. Please see the following article for how to best post readily consumable data.

    http://qa.sqlservercentral.com/articles/Best+Practices/61537/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 25, 2011 at 11:49 pm

    #1386286

    Thanks for your response. I too thought of using Pivot for this requriement. However, I am not going to do any aggregation. I just want to convert the column values in the second table as row heading in the final output. Is it possible to use pivot without doing the aggregation.

  • September 26, 2011 at 2:36 am

    #1386319

    HI you can use the sql-code below

    ----a=first table;b=second table

    declare @sql varchar(8000)

    set @sql=''

    select @sql=@sql+',max(case when skill='''+skill+''' then skill end) [skill'+skill+'],

    max(case when skill='''+skill+''' then proficiency end)[proficiency]' from (select * from B)t

    set @sql=STUFF(@sql,1,1,'')

    set @sql='select empid,ProjID,ApplicationGrp,'+@sql+' from (select a.* ,b.skill,proficiency from a left join b on a.empid = b.empid)t group by empid,ProjID,ApplicationGrp'

    print @sql

    exec(@sql)

  • September 26, 2011 at 5:59 am

    #1386404

    Thanks for your responses friends. I found that we must use aggregate function in the Pivot operator and i just used Min function just to retrieve the row and it worked without any issues.

    I created one pivot for converting the Skill row to column and created another pivot to convert Proficiency row to column and joined these 2 pivots.

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

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