Help with t-sql - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with t-sql

Author

Topic

Nylren
Starting Member

7 Posts

Posted - 2011-02-11 : 15:19:34

Here is my table:

File
---------
FileID(pk)
Name
Url
Year

I am trying to display this in html
_______________________
|.....Picture....Book.|
|2008 ...x........x...|
|2011....x........x...|
-----------------------

Year -> 2008, 2011
Url -> the 'x's

So, I am trying to build a T-SQL to select that.

Here is what I have tried:

SELECT
(SELECT Url FROM File WHERE (Name = 'Picture') AND (FileID = 1)) AS PictureUrl,
(SELECT Url FROM File WHERE (Name = 'Book') AND (FileID = 1)) AS Book Url
FROM DUAL

Thank you for any help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-11 : 15:27:56

This is a Microsoft SQL Server forum.
Oracle question can be made over at www.dbforums.com

N 56°04'39.26"
E 12°55'05.63"

Nylren
Starting Member

7 Posts

Posted - 2011-02-11 : 15:34:10

That's T-SQL (SQL SERVER), like I said in the message.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-11 : 15:39:29

Why are you referencing DUAL? That's Oracle syntax.

The following will work:

SELECT Year, MAX(CASE WHEN Name='Picture' THEN URL END) Picture,
MAX(CASE WHEN Name='Book' THEN URL END) Book
FROM File WHERE FileID=1
GROUP BY Year

You should also investigate the PIVOT keyword in SQL Server Books Online.

Nylren
Starting Member

7 Posts

Posted - 2011-02-11 : 15:49:39

Thank you, will give it a shot

Nylren
Starting Member

7 Posts

Posted - 2011-02-12 : 09:47:01

Hi, again.

The SQL worked just fine. Thank you.

Now I am trying to figure it out. I dont understand the purpose of the Max statement in this sql. Can anyone help me out?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources