Post reply

Create Columns using Row Data

  • December 5, 2011 at 10:52 am

    #251592

    Hi Guys,

    I need to find a way where I can dynamically create columns for the data rows. I need to create a proc or function to do something like below:

    Create Table dbo.EmpProj

    ( EmpID int,

    ProjectID int,

    ProjRole varchar(1000),

    HoursWorked int

    )

    Insert into EmpProj Values(1, 1, 'Create Etl', 85)

    Insert into EmpProj Values(1, 2, 'Analyze Data', 20)

    Insert into EmpProj Values(1, 3, 'Create Update Processes', 120)

    Insert into EmpProj Values(2, 5, 'QA', 30)

    Insert into EmpProj Values(2, 1, 'Test Scripts', 25)

    Insert into EmpProj Values(2, 7, 'Test ETL', 16)

    Insert into EmpProj Values(3, 2, 'Create Documents', 40)

    Create Table dbo.Project

    ( ProjectID int,

    ProjectName varchar(1000)

    )

    Insert into Project Values(1, 'ABC Project')

    Insert into Project Values(2, 'AAA Project')

    Insert into Project Values(3, 'XYZ Project')

    Insert into Project Values(4, 'Test Project')

    Insert into Project Values(5, 'OOO Project')

    Insert into Project Values(6, 'PETProject')

    Insert into Project Values(7, 'AlB Project')

    Insert into Project Values(8, 'ZXW Project')

    --This is the output I need using the data in the above two tables:

    --I need to create a function or SP where I should be able to Pass EmpID and get the every project's information in columns

    --And EmpID can be associated with serveral projects And I cannot hardcode project IDs as it will not be static and increase with time for newer projects!!!!!

    --For Example for for EmpID = 1 we will have the output as below:

    EmpID ProjectName_1 ProjRole_1 HoursWorked_1 ProjectName_2 ProjRole_2 HoursWorked_2 ProjectName_3 ProjRole_3 HoursWorked_3

    --==================================================================================================================================================

    1 ABC Project Create Etl 85 AAA Project Analyze Data 20 XYZ Project Create Update Processes 120

    Please suggest me the best way to tackle this type of request if we can in SQL?

    Thanks a lot for taking time in responding this...

  • December 5, 2011 at 11:08 am

    #1416900

    So you would need to use dynamic SQL to build either a crosstab query or use the PIVOT operator. Take a crack at it.

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • December 5, 2011 at 11:49 am

    #1416956

    Thanks bt for your response... I will take a look at those.

  • December 5, 2011 at 2:01 pm

    #1417023

    I was able to do hardcode the column and transpose rows to column; however, couldn't do it dynamically... will keep trying.

  • December 5, 2011 at 2:14 pm

    #1417035

    Do a Google search on "dynamic sql pivot" and you will find lots of info. Just make sure if you use dynamic sql to use sp_executesql. You'll find bad examples where that is not the case.

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

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