convert rows to columns in query

Question

Post reply

convert rows to columns in query

elham_azizi_62

SSCertifiable

Points: 5095
More actions
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.

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 2

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

http://www.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://www.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)

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 3

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.

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

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

Dee-ZA Old Hand Points: 347 More actions · Answer 7

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]

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 8

hello J Livingston.

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

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

Dee-ZA Old Hand Points: 347 More actions · Answer 9

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 AlipourNULL 2526961 NULL

1478 sara Emami548745 NULL NULL

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 10

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)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

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

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 12

dear J Livingston.

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 14

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