Help me tidy up this SQL statement...

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

Help me tidy up this SQL statement...

Author

Topic

Neil Duncan
Starting Member

2 Posts

Posted - 2007-11-07 : 11:05:20

Hi All,

I have three select statements, which look like this:
SELECT PhraseID, strPhrase FROM Phrase WHERE (LanguageID = ???)

I now want to display all of these columns side by side... The only way I've managed to do this is as follows:
SELECT a.PhraseID, a.blah, b.blah, c.blah FROM
(SELECT PhraseID, strPhrase as blah FROM Phrase WHERE (LanguageID = 10)) a,
(SELECT PhraseID, strPhrase AS blah FROM Phrase WHERE (LanguageID = 15)) b,
(SELECT PhraseID, strPhrase AS blah FROM Phrase WHERE (LanguageID = 17)) c
WHERE a.PhraseID = b.PhraseID AND a.PhraseID = c.PhraseID

This looks like a bit of a kludgy way of doing it, and I'm sure I'm missing a trick!

Is there a better approach?

Thanks,
Neil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-07 : 11:08:13

[code]SELECT PhraseID,
CASE WHEN LanguageID = 10 THEN strPhrase END AS blaha,
CASE WHEN LanguageID = 15 THEN strPhrase END AS blahb,
CASE WHEN LanguageID = 17 THEN strPhrase END AS blahc
FROM Phrase[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 11:22:58

[code]-- SQL Server 2000
SELECT PhraseID,
MAX(CASE WHEN LanguageID = 10 THEN strPhrase ELSE '' END) AS blahA,
MAX(CASE WHEN LanguageID = 15 THEN strPhrase ELSE '' END) AS blahB,
MAX(CASE WHEN LanguageID = 17 THEN strPhrase ELSE '' END) AS blahC
FROM Phrase
GROUP BY PhraseID

-- SQL Server 2005
SELECT p.PhraseID,
p.[10] AS blahA,
p.[15] AS blahB,
p.[17] AS blahC
FROM Phrase AS y
PIVOT (
MAX(y.strPhrase) FOR y.LanguageID IN ([10], [15], [17])
) AS p[/code]

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-07 : 11:41:10

Oops ! where are my group by and aggregate function