Rows to columns

Question

Post reply

Rows to columns

peygham

Say Hey Kid

Points: 707
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

fausto-1057143

Newbie

Points: 8

September 21, 2010 at 8:41 am

#1224885

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

The Dixie Flatline SSC Guru Points: 53353 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 Say Hey Kid Points: 707 More actions · Answer 4

peygham

Say Hey Kid

Points: 707

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 Say Hey Kid Points: 707 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

T1ID Title

1 Book

2 Magazine

Table2

T2ID T1ID Amt T3ID T4ID

1 1 10 1 1

2 1 20 2 2

3 1 30 3 2

Table3(lookup)

T3ID Description

1 Bound

2 Unbound

3 Paper

Table4(lookup)

T4ID Status

1 Unknown

2 Known

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

T1ID Unknown Known

1 Bound $10 Unbound $20; Paper $30

The Dixie Flatline SSC Guru Points: 53353 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 Say Hey Kid Points: 707 More actions · Answer 8

peygham

Say Hey Kid

Points: 707

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