Rows to columns

Question

Post reply

Rows to columns

peygham

Right there with Babe

Points: 757
More actions
September 21, 2010 at 8:06 am

#228142

Thanks in advance for any help you can provide.
I have the following;
ID Desc Amt Status
1 Item1 10 Unknown
1 Item2 20 Unknown
1 Item3 15 Known
2 Item1 25 Unknown
2 Item2 10 Known
2 Item3 15 Known
I want the following;
ID Known Unknown
1 Item3 - 15 Item1 - 10
Item2 - 20
2 Item2 - 10 Item1 - 25
Item3 - 15

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

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

fausto-1057143 Newbie Points: 8 More actions · Answer 1

See Pivot and Unpivot http://msdn.microsoft.com/en-us/library/ms177410.aspx

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

You can also search this site for articles or threads with the keyword "Crosstab" to see the old school way of converting rows to columns using SUM(CASE statements) and GROUP BY .

There are lots of examples out there. Your question is a very common one.:-)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

Have a read through Jeff Moden's excellent article[/url]. If you are still experiencing difficulty, then post again. It's a little tough going to begin with but you'll soon get the hang of it.

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

peygham Right there with Babe Points: 757 More actions · Answer 4

peygham

Right there with Babe

Points: 757

September 21, 2010 at 9:39 am

#1224971

Thank you all. I will follow each advice.

thecoding SSC Veteran Points: 211 More actions · Answer 5

Thanks in advance for any help you can provide.
I have the following;
ID Desc Amt Status
1 Item1 10 Unknown
1 Item2 20 Unknown
1 Item3 15 Known
2 Item1 25 Unknown
2 Item2 10 Known
2 Item3 15 Known
I want the following;
ID Known Unknown
1 Item3 - 15 Item1 - 10 Item2 - 20
2 Item2 - 10 Item1 - 25 Item3 - 15

PIVOT involves Aggregating process, does not fit this case.

You can use subquery to solve the problem.

peygham Right there with Babe Points: 757 More actions · Answer 6

Very informative articles but I feel as though I have not explained my problem clearly. My result set is derived from four tables, two are lookup tables. The relation is one to many. My goal is to combine (Concatenate) the many side into one row rendering it as one to one.

Table1

T1IDTitle

1Book

2Magazine

Table2

T2IDT1IDAmtT3IDT4ID

111011

212022

313032

Table3(lookup)

T3IDDescription

1Bound

2Unbound

3Paper

Table4(lookup)

T4IDStatus

1Unknown

2Known

I want to produce a row that will include T1ID and Concatenate Description with Amount for every unique Status... like this.

T1IDUnknownKnown

1Bound $10Unbound $20; Paper $30

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Again, there are a lot of articles and forums out there if you just search on the keyword "Concatenate". There is a technique using FOR XML that should get the job done for you. I would suggest doing your table joins in a subquery or cte to get a result set of rows that have the individual descriptions you want, then use FOR XML to build concatenated rows from that result set.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

peygham Right there with Babe Points: 757 More actions · Answer 8

peygham

Right there with Babe

Points: 757

September 21, 2010 at 1:54 pm

#1225203

Thank you. I will continue looking.

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

Bob's made some excellent points there. In case you return to ask a few more questions - please do if you get stuck - here's a bit of code which builds some tables (or rather, CTE's) and data for folks to play with and run potential solutions against. It gives everyone a level playing field for comparing results. What I recommend you do is extend the tables to create a variety of different groupings resulting in several concatenated rows of final output.

;WITH Table1 AS (

SELECT 1 AS T1ID, 'Book' AS Title UNION ALL

SELECT 2, 'Magazine'),

Table2 AS (

SELECT 1 AS T2ID, 1 AS T1ID, 10 AS Amt, 1 AS T3ID, 1 AS T4ID UNION ALL

SELECT 2, 1, 20, 2, 2 UNION ALL

SELECT 3, 1, 30, 3, 2),

Table3 AS (

SELECT 1 AS T3ID, 'Bound' AS [Description] UNION ALL

SELECT 2, 'Unbound' UNION ALL

SELECT 3, 'Paper'),

Table4 AS (SELECT 1 AS T4ID, 'Unknown' AS [Status] UNION ALL SELECT 2, 'Known')

SELECT t2.T2ID, t2.amt, t1.Title, t3.[Description], t4.[Status]

FROM Table2 t2

INNER JOIN Table1 t1 ON t1.T1ID = t2.T1ID

INNER JOIN Table3 t3 ON t3.T3ID = t2.T3ID

INNER JOIN Table4 t4 ON t4.T4ID = t2.T4ID

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