November 5, 2008 at 4:22 am
I have a table in the following structure:
CREATE TABLE [dbo].[Table1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [varchar](128) NOT NULL,
[Col2] [varchar](128) NOT NULL,
[Col3] [datetime] NOT NULL,
[Col4] [int] NOT NULL);
Into this I have data in the following format:
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Orange','Pips','2007-01-01',5)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Orange','Juice','2007-01-01',2)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Orange','Peel','2007-01-01',10)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Apple','Core','2007-01-01',1)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Apple','Skin','2007-01-01',9)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Orange','Pips','2007-01-02',6)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Orange','Juice','2007-01-02',3)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Orange','Peel','2007-01-02',11)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Apple','Core','2007-01-02',2)
INSERT INTO Table1 (col1, col2, col3,col4)
VALUES ('Apple','Skin','2007-01-02',10)
What I want to do is pass in the word Apple and have the following table generated:
Core Skin
1st Jan 2007 1 9
2nd Jan 2007 2 10
Whereas if I pass in Orange...
Pips Juice Peel
1st Jan 2007 5 2 10
2nd Jan 2007 6 3 11
Any help would be appreciated - thanks.
November 5, 2008 at 6:14 am
I think you would be better off doing the pivot in the front end.
If you really want to do it in the db I suspect you will have to resort to dynamic SQL.
Something like:
DECLARE @col1 varchar(20)
    ,@SQLString varchar(8000)
SET @col1 = 'Orange'
SET @SQLString = 'SELECT Col3 '
SELECT @SQLString = @SQLString
    + ',SUM(CASE WHEN col2 = ''' + col2 + ''' THEN col4 END) AS ' + col2 + ' '
FROM
(
    SELECT DISTINCT col2
    FROM dbo.Table1
    WHERE col1 = @col1
) D
SET @SQLString = @SQLString
    + 'FROM dbo.Table1 T1 '
    + 'WHERE col1 = ''' + @col1 + ''' '
    + 'GROUP BY Col3'
EXEC (@SQLString)
November 5, 2008 at 6:23 am
EXEC dynamic_pivot
'select col3,col4 from table1 where col1=''apple''','col2','sum(col4)'
Get procedure from http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Failing to plan is Planning to fail
November 6, 2008 at 8:09 am
I'm thinking perhaps the easiest way would be to just connect in from Excel and then do a pivot table on the data returned, so you can just query the raw data with nothing more than selecting only those records that are 'Apple' or 'Orange' (or whatever else appears in that column).
Just setting up the matrix control in Reporting Services or a crosstab in Crystal Reports would probably be more complicated.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2008 at 7:27 pm
I've been playing with using a dynamic PIVOT for your case, and it seems like it's going to be a little messy.
I think Ken's solution is the way to go.
November 7, 2008 at 12:58 am
ggraber (11/6/2008)
I've been playing with using a dynamic PIVOT for your case, and it seems like it's going to be a little messy.I think Ken's solution is the way to go.
Also see my blog entry
Failing to plan is Planning to fail
November 7, 2008 at 1:16 am
in SQL 2000, there is no PIVOT command, but you can get the data like PIVOT for more information see SQL BOL.
in SQL 2005 and above version PIVOT command exists, you can directly use PIVOT function.
November 7, 2008 at 5:27 am
Madhivanan (11/7/2008)
ggraber (11/6/2008)
I've been playing with using a dynamic PIVOT for your case, and it seems like it's going to be a little messy.I think Ken's solution is the way to go.
Also see my blog entry
I looked at your blog entry. However, your dynamic_pivot stored procedure will not work for this example. The OP needs to have one row per fruit type & date. Which starts to make things messy.
Kishore.P (11/7/2008)
in SQL 2000, there is no PIVOT command, but you can get the data like PIVOT for more information see SQL BOL.in SQL 2005 and above version PIVOT command exists, you can directly use PIVOT function.
No, he can not use a straight pivot in this case. He would need to use a dynamic one, and it will be unnecessarily complex because of the extra group by.
November 7, 2008 at 5:36 am
I looked at your blog entry. However, your dynamic_pivot stored procedure will not work for this example. The OP needs to have one row per fruit type & date. Which starts to make things messy.
Did you try it?
It exactly works as per OP's expected result
Failing to plan is Planning to fail
November 7, 2008 at 5:46 am
Madhivanan (11/7/2008)
Did you try it?It exactly works as per OP's expected result
I just tried it again, and you are correct, it works perfectly.
I don't know why I couldn't get it to work last night. :unsure:
November 7, 2008 at 5:55 am
ggraber (11/7/2008)
Madhivanan (11/7/2008)
Did you try it?It exactly works as per OP's expected result
I just tried it again, and you are correct, it works perfectly.
I don't know why I couldn't get it to work last night. :unsure:
Well. Also note that the procedure is dynamic and you can change the pivot or summary columns as you wish 🙂
Failing to plan is Planning to fail
November 7, 2008 at 5:58 am
Madhivanan (11/7/2008)
ggraber (11/7/2008)
Well. Also note that the procedure is dynamic and you can change the pivot or summary columns as you wish 🙂
Yes, I think it's very cool.
I've done a bunch of dynamic pivots at work, and your stored procedure would be very useful.
Actually, I just sent out a link to your article to the whole dev team over here... 🙂
November 10, 2008 at 6:45 am
ggraber (11/7/2008)
Madhivanan (11/7/2008)
ggraber (11/7/2008)
Well. Also note that the procedure is dynamic and you can change the pivot or summary columns as you wish 🙂Yes, I think it's very cool.
I've done a bunch of dynamic pivots at work, and your stored procedure would be very useful.
Actually, I just sent out a link to your article to the whole dev team over here... 🙂
Thanks 🙂
Failing to plan is Planning to fail
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply