Combine multiple rows into single SQL record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine multiple rows into single SQL record

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-01-28 : 12:32:18

Hello:

I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.

productID SectionID statusID
10 1 0
10 2 1
10 3 2
10 4 1
10 5 3
10 6 1
11 1 0
11 2 1
11 3 2
11 7 3
11 8 3

Need to return two rows with the respective values for each section.

productID section1 section2 section3 section4 section5 section6 section7 section8
10 0 1 2 1 3 1
11 0 1 2 3 3

Any information or if you can point me in the right direction would be appreciated.

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-28 : 12:37:21

Search this forum for CROSS TAB reports.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 12:37:28

Check for syntax of PIVOT in bol. Try to build a string of distinct Section IDs from table and use this with PIVOT to form a dynamic SQL string.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 12:41:20

Or you could just use case statements.

SELECT  productID,
        MAX(CASE WHEN SectionID = 1 THEN statusID
            END) AS section1,
        MAX(CASE WHEN SectionID = 2 THEN statusID
            END) AS section2,
        MAX(CASE WHEN SectionID = 3 THEN statusID
            END) AS section3,
        MAX(CASE WHEN SectionID = 4 THEN statusID
            END) AS section4,
        MAX(CASE WHEN SectionID = 5 THEN statusID
            END) AS section5,
        MAX(CASE WHEN SectionID = 6 THEN statusID
            END) AS section6,
        MAX(CASE WHEN SectionID = 7 THEN statusID
            END) AS section7,
        MAX(CASE WHEN SectionID = 8 THEN statusID
            END) AS section8
FROM    [YourTable]
GROUP BY productID

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-01-28 : 13:15:34

Thanks everyone for the cross tab, pivot and table info. I'm now going in the right direction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources