Howto map Rows to Columns - SQL Server Forums

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

Howto map Rows to Columns

Author

Topic

amorph
Starting Member

5 Posts

Posted - 2010-02-24 : 11:29:09

Hi , might be a stupid question but how do i do the following:

I got 2 tables - one with products and another one with product-descriptions in different languages:

Table Products:
ID | ...

Table ProductDescriptions:
ProductID | LanguageID | Description

Now the actual question: how do i get a product with the descriptions for e.g. 3 different languages (so where languageid=1, languageid=2 and languageid=3). So the result should look like this:

ProductID | DescriptionLanguag1 | DescriptionLanguage2 | DescriptionLanguage3

Thanks and regards
amorph

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 11:30:58

Refer to this very recent thread.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140383

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:32:03

[code]SELECT ProductID,
MAX(CASE WHEN LanguageID=1 THEN Description ELSE NULL END) AS DescriptionLanguag1,
MAX(CASE WHEN LanguageID=2 THEN Description ELSE NULL END) AS DescriptionLanguag2,
MAX(CASE WHEN LanguageID=3 THEN Description ELSE NULL END) AS DescriptionLanguag3
FROM ProductDescriptions
GROUP BY ProductID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amorph
Starting Member

5 Posts

Posted - 2010-02-24 : 12:05:12

Thank you very much for the answer. But what if i need some more columns from the product-table (e.g. producttype)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 12:07:25

quote:
Originally posted by amorph

Thank you very much for the answer. But what if i need some more columns from the product-table (e.g. producttype)?

show what you're expecting as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amorph
Starting Member

5 Posts

Posted - 2010-02-24 : 12:17:37

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 12:24:12

quote:
Originally posted by amorph

Okay, so the Product-Table looks like ID | ProductType | Status
and the end result should look like this:

ProductID | ProductType | Status | DescriptionLanguag1 | DescriptionLanguage2 | DescriptionLanguage3

You have to include it in the SELECT...any trouble with that?

SELECT ProductID,ProductType,Status,
MAX(CASE WHEN LanguageID=1 THEN Description ELSE NULL END) AS DescriptionLanguag1,
MAX(CASE WHEN LanguageID=2 THEN Description ELSE NULL END) AS DescriptionLanguag2,
MAX(CASE WHEN LanguageID=3 THEN Description ELSE NULL END) AS DescriptionLanguag3 
FROM ProductDescriptions
GROUP BY ProductID,ProductType,Status

amorph
Starting Member

5 Posts

Posted - 2010-02-24 : 12:25:50

Didnt think its that easy. Thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 12:29:23

quote:
Originally posted by amorph

Didnt think its that easy. Thanks a lot!

can there be same productid records with different status values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amorph
Starting Member

5 Posts

Posted - 2010-02-25 : 02:52:05

No.
but why do you use MAX? What i need is a Result which contains every product from the product table along with its Descriptions ...

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:02:51

quote:
Originally posted by amorph

No.
but why do you use MAX? What i need is a Result which contains every product from the product table along with its Descriptions ...

Thank you very much!

Then its ok

I use MAX just to get all the values onto a single row. Cross tabbing is actually an aggregation operation thats why you need to use MAX with GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources