Display xColumns in Row? - 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 2005 Forums

Transact-SQL (2005)

Display xColumns in Row?

Author

Topic

iispro
Starting Member

2 Posts

Posted - 2009-04-15 : 13:43:02

I have the following Query

SELECT t.Id AS ID1 ,t.Link AS Link1 ,t.Description As Desc1
FROM Gallery t
INNER JOIN GalleryMap tm
ON tm.GalleryId = t.ID
WHERE tm.CatID = 36

The Results are like this

ID1 Link1 Desc1
1 Link_1 Desc_1
2 Link_2 Desc_2
3 Link_3 Desc_3
4 Link_4 Desc_4
5 Link_5 Desc_5

I would like to display the results like this

ID1 Link1 Desc1 ID2 Link2 Desc2 ID3 Link3 Desc3
1 Link_1 Desc_1 2 Link_2 Desc_2 3 Link_3 Desc_3
4 Link_4 Desc_4 5 Link_5 Desc_5

Any idea how to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:47:49

[code]
SELECT NewID,
MAX(CASE WHEN Seq=1 THEN Link1 ELSE NULL END) AS Link1,
MAX(CASE WHEN Seq=1 THEN Desc1 ELSE NULL END) AS Desc1,
MAX(CASE WHEN Seq=2 THEN Link1 ELSE NULL END) AS Link2,
MAX(CASE WHEN Seq=2 THEN Desc1 ELSE NULL END) AS Desc2,
MAX(CASE WHEN Seq=3 THEN Link1 ELSE NULL END) AS Link3,
MAX(CASE WHEN Seq=3 THEN Desc1 ELSE NULL END) AS Desc3
FROM
(SELECT (ID1 / 3) + 1 AS NewID,Link1,Desc1,ROW_NUMEBR() OVER (PARTITION BY (ID1 / 3)+ 1 ORDER BY ID1) AS Seq
FROM Table
)t
GROUP BY NewID
[/code]

iispro
Starting Member

2 Posts

Posted - 2009-04-15 : 14:37:21

I Modified the query to the following and get 54 rows total. My initial query returned 112 rows. I am getting Null's in random areas in the second and third set of data.

I tested your initial query without a join and it worked for most part. The first and Last rows columns 3's had Null's. What I would like is every column filled and Nulls and the last columns of the last row if needed.

SELECT NewID,
MAX(CASE WHEN Seq=1 THEN Id ELSE NULL END) AS ID1,
MAX(CASE WHEN Seq=1 THEN Link ELSE NULL END) AS Link1,
MAX(CASE WHEN Seq=1 THEN Description ELSE NULL END) AS Desc1,
MAX(CASE WHEN Seq=2 THEN Id ELSE NULL END) AS ID2,
MAX(CASE WHEN Seq=2 THEN Link ELSE NULL END) AS Link2,
MAX(CASE WHEN Seq=2 THEN Description ELSE NULL END) AS Desc2,
MAX(CASE WHEN Seq=3 THEN Id ELSE NULL END) AS ID3,
MAX(CASE WHEN Seq=3 THEN Link ELSE NULL END) AS Link3,
MAX(CASE WHEN Seq=3 THEN Description ELSE NULL END) AS Desc3
FROM
(SELECT (ID / 3) + 1 AS NewID,Link,Description,ROW_NUMBER() OVER (PARTITION BY (ID / 3)+ 1 ORDER BY ID) AS Seq
FROM Gallery t
INNER JOIN GalleryMap tm
ON tm.GalleryId = t.ID
WHERE tm.CatID = 36
) s
GROUP BY NewID

Bhavesh Patel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 10:57:53

is your ID1 value always continuos?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources