Help with Pivot Query - 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

Transact-SQL (2000)

Help with Pivot Query

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2008-01-25 : 15:41:21

I have two tables and need to create a pivot.

Tbl1 = Date, Ticker, IndId
12/31/2006, Msft, 1
12/31/2006, GE, 2
12/31/2006, WFC, 3
1/31/2007, Msft, 1
1/31/2007, GE, 2
12/31/2007, WFC, 3

Tbl2 = IndId, IndName
1, Tech
2, Cons
3, Bank

I have this data for 20 years so the industries change as tickers are moved in/out

I want to have a summary table with distinct ticker and a column for each date (monthly for 20 years) with the data holding the indname from tbl2. It would look like:

Ticker 12/06 01/07
Msft Tech Tech
GE Cons Cons
WFC Bank Bank

Any help is appreciated as I can't seem to figure this one out.
I was using cases for each date but I would think there would be an easier way.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-26 : 11:24:52

It seems like you are trying to show the data on some kind of report. If thats the case you are do this more easily at front end. Can i ask what front end you are using?

legacyvbc
Starting Member

37 Posts

Posted - 2008-01-28 : 10:58:09

I am just bringing it into excel. If that is the easier way to do it then I can just proceed as such but if there is a way to write a query to do it any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 11:03:25

quote:
Originally posted by legacyvbc

I am just bringing it into excel. If that is the easier way to do it then I can just proceed as such but if there is a way to write a query to do it any help is appreciated.

Pivoting in SQL 2000 is a bit of pain. I think it would be nice if you could try to bring data to excel and then try to Pivot. Excel has a in built pivot functionality. You may find this link helpful:-

[url]http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:11:14

That's over 240 columns!

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

legacyvbc
Starting Member

37 Posts

Posted - 2008-01-28 : 16:19:54

i know it is a lot of columns. I would actually be creating a procedure so it would typically be a subset of dates. as an example, Jun-06 - Jun-07 which would only be 12 columns. That is why I am trying to do this. Thanks anyway.

legacyvbc
Starting Member

37 Posts

Posted - 2008-01-28 : 16:19:58

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 02:11:33

It is quite easy to write a query that will show the next 12 months in a PIVOT style.

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

legacyvbc
Starting Member

37 Posts

Posted - 2008-01-29 : 13:43:36

how would I write the query? do I just use the case for each month. Also, how do I join the tables so instead of the id showing it will display the name?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:38:58

Use something like this and name the column at the front-end!

DECLARE @Tbl1 TABLE (Date DATETIME, Ticker VARCHAR(10), IndID INT)

SET DATEFORMAT MDY

INSERT	@Tbl1
SELECT	'12/31/2006', 'Msft', 1 UNION ALL
SELECT	'12/31/2006', 'GE', 2 UNION ALL
SELECT	'12/31/2006', 'WFC', 3 UNION ALL
SELECT	'1/31/2007', 'Msft', 1 UNION ALL
SELECT	'1/31/2007', 'GE', 2 UNION ALL
SELECT	'12/31/2007', 'WFC', 3

DECLARE @Tbl2 TABLE (IndID INT, IndName VARCHAR(10))

INSERT	@Tbl2
SELECT	1, 'Tech' UNION ALL
SELECT	2, 'Cons' UNION ALL
SELECT	3, 'Bank'

DECLARE	@FirstDate DATETIME

SET	@FirstDate = '20061203'

SELECT		t1.Ticker,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 0 THEN t2.IndName ELSE '' END) AS m00,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 1 THEN t2.IndName ELSE '' END) AS m01,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 2 THEN t2.IndName ELSE '' END) AS m02,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 3 THEN t2.IndName ELSE '' END) AS m03,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 4 THEN t2.IndName ELSE '' END) AS m04,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 5 THEN t2.IndName ELSE '' END) AS m05,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 6 THEN t2.IndName ELSE '' END) AS m06,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 7 THEN t2.IndName ELSE '' END) AS m07,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 8 THEN t2.IndName ELSE '' END) AS m08,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 9 THEN t2.IndName ELSE '' END) AS m09,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 10 THEN t2.IndName ELSE '' END) AS m10,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 11 THEN t2.IndName ELSE '' END) AS m11
FROM		@Tbl1 AS t1
INNER JOIN	@Tbl2 AS t2 ON t2.IndID = t1.IndID
GROUP BY	t1.Ticker
ORDER BY	t1.Ticker

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:40:58

The stored procedure should look something like this

CREATE PROCEDURE dbo.uspShowTicker
(
	@FirstDate DATETIME
)
AS

SET NOCOUNT ON

SELECT		t1.Ticker,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 0 THEN t2.IndName ELSE '' END) AS m00,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 1 THEN t2.IndName ELSE '' END) AS m01,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 2 THEN t2.IndName ELSE '' END) AS m02,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 3 THEN t2.IndName ELSE '' END) AS m03,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 4 THEN t2.IndName ELSE '' END) AS m04,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 5 THEN t2.IndName ELSE '' END) AS m05,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 6 THEN t2.IndName ELSE '' END) AS m06,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 7 THEN t2.IndName ELSE '' END) AS m07,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 8 THEN t2.IndName ELSE '' END) AS m08,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 9 THEN t2.IndName ELSE '' END) AS m09,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 10 THEN t2.IndName ELSE '' END) AS m10,
		MAX(CASE WHEN DATEDIFF(MONTH, @FirstDate, t1.Date) = 11 THEN t2.IndName ELSE '' END) AS m11
FROM		@Tbl1 AS t1
INNER JOIN	@Tbl2 AS t2 ON t2.IndID = t1.IndID
GROUP BY	t1.Ticker
ORDER BY	t1.Ticker

Call the SP from your client.
And since you know the value of the parameter passed to the SP, you can easily rename the columns in your application.

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

legacyvbc
Starting Member

37 Posts

Posted - 2008-01-29 : 19:52:36

awesome, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources