Display data horizontally 2 - 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 2000 Forums

SQL Server Development (2000)

Display data horizontally 2

Author

Topic

jrl55
Starting Member

4 Posts

Posted - 2010-04-28 : 08:00:54

This is a variation on a recent post by scabral7, but I just can't figure it out.

I have this in the table:

Jan aaa
Jan bbb
Feb aaa
Feb ccc
Feb ddd
Mar bbb
Mar eee

I need this in the query result:

Jan Feb Mar
aaa aaa bbb
bbb ccc eee
ddd

Any help would be greatly welcome!
Many thanks
John

jrl55
Starting Member

4 Posts

Posted - 2010-04-28 : 08:42:59

Sorry, the 'ddd' in the query result should be in the Feb column of course.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-28 : 08:51:38

See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 09:34:23

quote:
Originally posted by madhivanan

See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

I don't think that's going to be much help, as there are no aggregates in the OP. Here's something that may help:

Suppose the data is stored in a table called "Test", with columns "Mon" and "Data", then:

CREATE TABLE #tmpJan (
	ID INT IDENTITY (1, 1),
	Data CHAR(3)
)

CREATE TABLE #tmpFeb (
	ID INT IDENTITY (1, 1),
	Data CHAR(3)
)

CREATE TABLE #tmpMar (
	ID INT IDENTITY (1, 1),
	Data CHAR(3)
)

INSERT INTO #tmpJan (Data)
SELECT Data 
FROM Test
WHERE Mon = 'Jan'
ORDER BY Data

INSERT INTO #tmpFeb (Data)
SELECT Data 
FROM Test
WHERE Mon = 'Feb'
ORDER BY Data

INSERT INTO #tmpMar (Data)
SELECT Data 
FROM Test
WHERE Mon = 'Mar'
ORDER BY Data

SELECT Jan.Data AS Jan, Feb.Data AS Feb, Mar.Data AS Mar
FROM #tmpJan Jan
FULL OUTER JOIN #tmpFeb Feb
	ON Jan.ID = Feb.ID
FULL OUTER JOIN #tmpMar Mar
	ON Jan.ID = Mar.ID

DROP TABLE #tmpJan 
DROP TABLE #tmpFeb 
DROP TABLE #tmpMar

It's a little messy, and would be much simpler on a 2005 server, with the use of ROW_NUMBER. Basically, it uses the IDENTITY column of each temp table to generate the row number, then joins on that row number.

Hope this helps.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 09:42:48

Actually, you'll need to be a little more clever with the joins. I think you'll need to use COALESCE, like this:

SELECT Jan.Data AS Jan, Feb.Data AS Feb, Mar.Data AS Mar, Apr.Data AS Apr, May.Data as May
FROM #tmpJan Jan
FULL OUTER JOIN #tmpFeb Feb
	ON Jan.ID = Feb.ID
FULL OUTER JOIN #tmpMar Mar
	ON COALESCE(Jan.ID, Feb.ID) = Mar.ID
FULL OUTER JOIN #tmpApr Apr
	ON COALESCE(Jan.ID, Feb.ID, Mar.ID) = Apr.ID
FULL OUTER JOIN #tmpMay May
	ON COALESCE(Jan.ID, Feb.ID, Mar.ID, Apr.ID) = May.ID

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

jrl55
Starting Member

4 Posts

Posted - 2010-04-28 : 10:16:55

Thank you very much for your suggestions. I'll try these out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 10:42:41

wont this be enough?


SELECT [Jan],[Feb],[Mar]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Mon ORDER BY Data ASC) AS Seq, Mon,Data
FROM Table
)t
PIVOT (MAX(Data) FOR Mon IN ([Jan],[Feb],[Mar]))p

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 11:16:28

quote:

Originally posted by visakh16

wont this be enough?


SELECT [Jan],[Feb],[Mar]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Mon ORDER BY Data ASC) AS Seq, Mon,Data
FROM Table
)t
PIVOT (MAX(Data) FOR Mon IN ([Jan],[Feb],[Mar]))p

Not in a SQL 2000 Forum.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:32:07

[code]SELECT
MAX(CASE WHEN Mon='Jan' THEN Data ELSE NULL END) AS [Jan],
MAX(CASE WHEN Mon='Feb' THEN Data ELSE NULL END) AS [Feb],
MAX(CASE WHEN Mon='Mar' THEN Data ELSE NULL END) AS [Mar]
FROM
(
SELECT (SELECT COUNT(*) FROM Table WHERE Mon=r.Mon AND Data < r.Data)+1 AS Seq, Mon,Data
FROM Table r
)t
GROUP BY Seq
[/code]

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

jrl55
Starting Member

4 Posts

Posted - 2010-04-28 : 11:49:08

Thanks to everybody for your quick and helpful replies, but thanks particularly to 'DBA in the making' - you've solved a problem I've been grappling with for a couple of days.

I'm new to this forum but I already think it's great!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 12:09:50

quote:

Originally posted by visakh16

SELECT 
MAX(CASE WHEN Mon='Jan' THEN Data ELSE NULL END) AS [Jan],
MAX(CASE WHEN Mon='Feb' THEN Data ELSE NULL END) AS [Feb],
MAX(CASE WHEN Mon='Mar' THEN Data ELSE NULL END) AS [Mar]
FROM
(
SELECT (SELECT COUNT(*) FROM Table WHERE Mon=r.Mon AND Data < r.Data)+1 AS Seq, Mon,Data
FROM Table r
)t
GROUP BY Seq

That will also work, but only if there are no duplicate values of Data for a given month.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 12:10:28

quote:
Originally posted by jrl55

Thanks to everybody for your quick and helpful replies, but thanks particularly to 'DBA in the making' - you've solved a problem I've been grappling with for a couple of days.

I'm new to this forum but I already think it's great!

No worries. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources