Post reply

convert rows to columns in query

  • September 13, 2015 at 5:38 am

    #320531

    hello all

    I have this query:

    SELECT Personelnumber,Name,LastName,Description,Amount FROM Perferaj

    Query's Output is:

    1188 Ali Ahmadi BaseSalary 1213232323

    1245 ahmad Alipour ExtraSalary 2526961

    1188 ali ahmadi ExtraSalary 54585

    1478 sara Emami BaseSalary 548745

    1188 Ali Ahmadi SpecialSalary 245832

    I need this Output:

    1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

    1245 ahmad Alipour Null Null ExtraSalary 2526961 Null Null

    1478 sara Emami BaseSalary 548745 Null Null Null Null

    pleas give me a query for this result.thanks for your help.

  • September 13, 2015 at 6:18 am

    #1826244

    see how this works to start with

    SELECT Personelnumber, Name, LastName,

    max(case when Description = 'BaseSalary' then 'basesalary' end ) ,

    max(case when Description = 'BaseSalary' then amount end) ,

    max(case when Description = 'ExtraSalary' then 'extrasalary' end) ,

    max(case when Description = 'ExtraSalary' then amount end) ,

    max(case when Description = 'SpecialSalary' then 'specialsalary' end ),

    max(case when Description = 'SpecialSalary' then amount end)

    FROM Perferaj

    GROUP BY Personelnumber, Name, LastName

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • September 13, 2015 at 8:22 am

    #1826245

    Along with JLS's fine answer above, please see the following article to understand how it works.

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

    For your next post, please see the following article for the best way to post sample data to entice people to help you.

    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 30, 2015 at 3:04 am

    #1830357

    hello.

    thanks for your reply.

    I already need this out put:

    1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

    1245 ahmad Alipour Null Null ExtraSalary 2526961 Null Null

    1478 sara Emami BaseSalary 548745 Null Null Null Null

    but Now need this out put:

    1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

    1245 ahmad Alipour ExtraSalary 2526961

    1478 sara Emami BaseSalary 548745

    I mean when a column is null it dosnot show.please change this query for this result.

    thank you.

  • September 30, 2015 at 3:44 am

    #1830365

    hello J Livingston SQL please change your query for my new out put.thank you.

  • September 30, 2015 at 4:52 am

    #1830377

    elham_azizi_62 (9/30/2015)

    hello.

    thanks for your reply.

    I already need this out put:

    1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

    1245 ahmad Alipour Null Null ExtraSalary 2526961 Null Null

    1478 sara Emami BaseSalary 548745 Null Null Null Null

    but Now need this out put:

    1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

    1245 ahmad Alipour ExtraSalary 2526961

    1478 sara Emami BaseSalary 548745

    I mean when a column is null it dosnot show.please change this query for this result.

    thank you.

    Use your presentation software for this.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 30, 2015 at 10:21 am

    #1830492

    elham_azizi_62 (9/30/2015)

    but Now need this out put:

    1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

    1245 ahmad Alipour ExtraSalary 2526961

    1478 sara Emami BaseSalary 548745

    I mean when a column is null it dosnot show.please change this query for this result.

    thank you.

    is your output all in one column?

    if not what are the column headers?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • October 1, 2015 at 4:22 am

    #1830687

    Try the code below and let me know if it will serve the same purpose as your desired output. Please replace the table name with your own.

    [font="Comic Sans MS"]SELECT [Personelnumber]

    ,[Name]

    ,[LastName]

    ,BaseSalary

    ,ExtraSalary

    ,SpecialSalary

    FROM (SELECT [Personelnumber]

    ,[Name]

    ,[LastName]

    ,[Description]

    ,[Amount]

    FROM [TrainingLocal].[dbo].[Perferaj]) AS Source

    PIVOT

    (

    SUM([Amount]) FOR [Description] IN (BaseSalary, ExtraSalary, SpecialSalary)

    ) PIV[/font]

  • October 5, 2015 at 2:20 am

    #1831308

    hello J Livingston.

    My output have more than 1 column and column's names are:

    PersonelNumber,FirstName,LastName,BaseSalary,BaseSalaryAmount,ExtraSalary,ExtraSalaryAmount,SpecialSalary,SpecialSalaryAmount.

  • October 5, 2015 at 4:27 am

    #1831321

    elham_azizi_62 (10/5/2015)

    hello J Livingston.

    My output have more than 1 column and column's names are:

    PersonelNumber,FirstName,LastName,BaseSalary,BaseSalaryAmount,ExtraSalary,ExtraSalaryAmount,SpecialSalary,SpecialSalaryAmount.

    The script I placed in my post produce this results:

    Personelnumber Name LastName BaseSalary ExtraSalary SpecialSalary

    1188 Ali Ahmadi 1213232323 54585 245832

    1245 ahmad Alipour NULL 2526961 NULL

    1478 sara Emami 548745 NULL NULL

  • October 5, 2015 at 5:29 am

    #1831336

    elham_azizi_62 (10/5/2015)

    hello J Livingston.

    My output have more than 1 column and column's names are:

    PersonelNumber,FirstName,LastName,BaseSalary,BaseSalaryAmount,ExtraSalary,ExtraSalaryAmount,SpecialSalary,SpecialSalaryAmount.

    It's time to stop guessing. Please see the article at the first link in my signature line below. It'll take all the guesswork out.

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

  • October 5, 2015 at 7:09 am

    #1831369

    elham_azizi_62 (10/5/2015)

    hello J Livingston.

    My output have more than 1 column and column's names are:

    PersonelNumber,FirstName,LastName,BaseSalary,BaseSalaryAmount,ExtraSalary,ExtraSalaryAmount,SpecialSalary,SpecialSalaryAmount.

    I dont understand......you previously asked for this

    1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

    1245 ahmad Alipour ExtraSalary 2526961

    1478 sara Emami BaseSalary 548745

    your two requirements dont match up....

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • October 5, 2015 at 11:20 pm

    #1831592

    dear J Livingston.

    I mean dynamic generating columns.in fact if my column is null,column isn't shown.

  • October 6, 2015 at 1:34 am

    #1831610

    elham_azizi_62 (10/5/2015)

    dear J Livingston.

    I mean dynamic generating columns.in fact if my column is null,column isn't shown.

    This only makes sense if the entire column is NULL. Think about it. What you appear to be asking for is that NULL values (in a column which also contains non-NULL values) don't appear as NULL but as blank. That's easy enough: convert any numeric values to character then replace all NULLs with an empty string. But like I said 23 posts ago, this is presentation and is a job for your presentation app.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 6, 2015 at 2:50 am

    #1831620

    elham_azizi_62 (10/5/2015)

    dear J Livingston.

    I mean dynamic generating columns.in fact if my column is null,column isn't shown.

    ....not entirely sure that I understand your requirements....but try this

    CREATE TABLE #sampledata(

    PersonID int NOT NULL,

    FirstName varchar(50) NULL,

    Surname varchar(50) NULL,

    SalaryType varchar(50) NULL,

    SalaryAmount int NULL

    )

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1188, N'Ali', N'Ahmadi', N'BaseSalary', 1213232323)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1245, N'ahmad', N'Alipour', N'ExtraSalary', 2526961)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1188, N'ali', N'ahmadi', N'ExtraSalary', 54585)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1478, N'sara', N'Emami', N'BaseSalary', 548745)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1188, N'Ali', N'Ahmadi', N'SpecialSalary', 245832)

    SELECT * FROM #sampledata;

    with cte as (

    SELECT

    PersonID

    , FirstName

    , Surname

    , SalaryType

    , SalaryAmount

    , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY SalaryType) AS rn

    FROM #sampledata

    )

    SELECT

    PersonID

    , FirstName

    , Surname

    , ISNULL(MAX(CASE WHEN rn = 1 THEN SalaryType END ) , '') as SalaryType

    , ISNULL(MAX(CASE WHEN rn = 1 THEN CAST(SalaryAmount as varchar(10)) END) , '') as SalaryAmount

    , ISNULL(MAX(CASE WHEN rn = 2 THEN SalaryType END ) , '') as SalaryType

    , ISNULL(MAX(CASE WHEN rn = 2 THEN CAST(SalaryAmount as varchar(10)) END) , '') as SalaryAmount

    , ISNULL(MAX(CASE WHEN rn = 3 THEN SalaryType END ) , '') as SalaryType

    , ISNULL(MAX(CASE WHEN rn = 3 THEN CAST(SalaryAmount as varchar(10)) END) , '') as SalaryAmount

    FROM cte

    GROUP BY PersonID, FirstName, Surname

    DROP TABLE #sampledata

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 15 posts - 1 through 15 (of 16 total)

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