SQL Turn Row data to Column

Question

Post reply

SQL Turn Row data to Column

Dee Dee-422077

Ten Centuries

Points: 1082
More actions
August 19, 2015 at 8:11 pm

#320745

Hi,
ItemID ItemName Price
1 Test 1 100
1 Test 1 200
2 Test 2 130
2 Test 2 150
3 Test 3 300
3 Test 3 400
...
How to write a query to bring price data to column so the Item is not duplicate any more.
The result should be:
Item ID ITemName PRice1 PRice2
1 Test1 100 200
2 Test2 130 150
3 Test3 300 400
Any help would be greatly appreciated.
THanks,
Dee

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

http://stackoverflow.com/questions/20057655/multiple-rows-in-one-column-sql-server

Have you seen this ?

Jayanth Kurup[/url]

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

Jayanth_Kurup (8/19/2015)
http://stackoverflow.com/questions/20057655/multiple-rows-in-one-column-sql-server
Have you seen this ?

That's concatenation rather than creating separate columns for common items.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Dee Dee-422077 (8/19/2015)
Hi,
ItemID ItemName Price
1 Test 1 100
1 Test 1 200
2 Test 2 130
2 Test 2 150
3 Test 3 300
3 Test 3 400
...
How to write a query to bring price data to column so the Item is not duplicate any more.
The result should be:
Item ID ITemName PRice1 PRice2
1 Test1 100 200
2 Test2 130 150
3 Test3 300 400
Any help would be greatly appreciated.
THanks,
Dee

What's the maximum number of possible prices for each ItemID? Or does that need to be dynamic?

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Quick static solution

😎

USE RDSTEEST;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;

CREATE TABLE dbo.TBL_SAMPLE_DATA

(

ItemID INT NOT NULL

,ItemName VARCHAR(20) NOT NULL

,Price DECIMAL(12,2) NOT NULL

);

INSERT INTO dbo.TBL_SAMPLE_DATA

(ItemID,ItemName,Price)

VALUES ( 1,'Test 1', 100)

,( 1,'Test 1', 200)

,( 2,'Test 2', 130)

,( 2,'Test 2', 150)

,( 3,'Test 3', 300)

,( 3,'Test 3', 400);

;WITH BASE_DATA AS

(

SELECT

SD.ItemID

,ROW_NUMBER() OVER

(

PARTITION BY SD.ItemID

ORDER BY (SELECT NULL)

) AS SD_RID

,SD.ItemName

,SD.Price

FROM dbo.TBL_SAMPLE_DATA SD

)

SELECT

BD.ItemID

,BD.ItemName

,MAX(CASE WHEN BD.SD_RID = 1 THEN BD.Price END) AS Price1

,MAX(CASE WHEN BD.SD_RID = 2 THEN BD.Price END) AS Price2

,MAX(CASE WHEN BD.SD_RID = 3 THEN BD.Price END) AS Price3

FROM BASE_DATA BD

GROUP BY BD.ItemID

,BD.ItemName

;

Results

ItemID ItemName Price1 Price2 Price3

------- --------- ------- ------- -------

1 Test 1 100.00 200.00 NULL

2 Test 2 130.00 150.00 NULL

3 Test 3 300.00 400.00 NULL

Dee Dee-422077 Ten Centuries Points: 1082 More actions · Answer 5

Hi Jeff,

Thanks for a quick reply.

The maximum price per itemid is 2.

Also the ItemID is dynamic

Thanks,

Dee

Dee Dee-422077 Ten Centuries Points: 1082 More actions · Answer 6

Thanks for the static solution.. But nn my table has thousand of rows so I really need the dynamic solution.

Best,

Dee

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

In that case I think you need to use the PIVOT Syntax within t-sql

@jeff Looks like a perfect example on using dynamixc SQL 🙂

Jayanth Kurup[/url]

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

Dee Dee-422077 (8/20/2015)
Hi Jeff,
Thanks for a quick reply.
The maximum price per itemid is 2.
Also the ItemID is dynamic
Thanks,
Dee

SELECT ItemID, ItemName,

Price1 = MIN(Price),

Price2 = MAX(Price)

FROM #TBL_SAMPLE_DATA

GROUP BY ItemID, ItemName

^{“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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Jayanth_Kurup (8/20/2015)
@jeff Looks like a perfect example on using dynamixc SQL 🙂

Nah. Not for this. It's a simple CROSSTAB or PIVOT problem, especially since it's known that there will only ever be two entries per item ID. CHRISM beat me to it on the solution (he usually does :-D) using a simple CROSSTAB.

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

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

Eirikur's CROSSTAB solution is an effective solution for when the maximum number of items is 3 and can be easily converted for larger known maximums. It's also easily converted to solve, using dynamic SQL, for when the maximum number of items is unknown.

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