Column to Row query

[erickwidya]

i got this value

KK + Q1 + Q2 + Q3
01 + 1 + 2 + 3
01 + 2 + 3 + 4
02 + 5 + 6 + 7
02 + 7 + 8 + 9

i can do pivot query so the result will be something like this

KK + Q1 + Q2 + Q3
01 + 3 + 5 + 7
02 12 + 14 + 16

but the report need to be used reversal format from the result above (Q1, Q2, Q3 as row where KK as Column)

Q1 + 3 + 12
Q2 + 5 + 14
Q3 + 7 + 16

sorry, don't how to format it here so hope u get the idea

i found a solution via UNION query before using pivot for each Q1, Q2, Q3 but just curious is there any other way

any idea?

thanks,

[kevchadders]

What are you using to display the report. e.g. Excel, Crystal, just CSV... etc
Also are you using any programming language which calls the query.

[erickwidya]

What are you using to display the report. e.g. Excel, Crystal, just CSV... etc
Also are you using any programming language which calls the query.

sorry, forgot to mention i'm using Crystal Reports 10 (field definition only) and VB 6.0..so it need to pass as Recordset

[riteshjain1982]

is crystal report's Cross Tab report will help?

[erickwidya]

is crystal report's Cross Tab report will help?

no, i don't think so..just like to figure it out in SQL query

btw, another info i'm using SQL SERVER 2005

[kevchadders]

As your using 2005 the PIVOT command may be the way to go if you want to do it via SQL. It was introduced in SQL Server 2005. Check link below and look it up in the help in your Management Studio.

http://sqlserver2000.databases.aspfa...vot-query.html

CREATE TABLE dbo.SalesByQuarter 
( 
    Y INT, 
    Q INT, 
    sales INT, 
    PRIMARY KEY (Y,Q) 
) 
GO 
 
INSERT dbo.SalesByQuarter(Y,Q,Sales) 
    SELECT 2003, 2, 479000 
    UNION SELECT 2003, 3, 321000 
    UNION SELECT 2003, 4, 324000 
    UNION SELECT 2004, 1, 612000 
    UNION SELECT 2004, 2, 524000 
    UNION SELECT 2004, 3, 342000 
    UNION SELECT 2004, 4, 357000 
    UNION SELECT 2005, 1, 734000 
GO 
 
SELECT Y,  
    [1] AS Q1, 
    [2] AS Q2, 
    [3] AS Q3, 
    [4] AS Q4 
FROM 
    (SELECT Y, Q, Sales 
        FROM SalesByQuarter) s PIVOT 
( 
    SUM(Sales) 
    FOR Q IN ([1],[2],[3],[4]) 
) p 
ORDER BY [Y] 
GO 
 
DROP TABLE dbo.SalesByQuarter 
GO

ps. Though you should really try to do it on the client side.

[erickwidya]

As your using 2005 the PIVOT command may be the way to go if you want to do it via SQL. It was introduced in SQL Server 2005. Check link below and look it up in the help in your Management Studio.

http://sqlserver2000.databases.aspfa...vot-query.html

CREATE TABLE dbo.SalesByQuarter 
( 
    Y INT, 
    Q INT, 
    sales INT, 
    PRIMARY KEY (Y,Q) 
) 
GO 
 
INSERT dbo.SalesByQuarter(Y,Q,Sales) 
    SELECT 2003, 2, 479000 
    UNION SELECT 2003, 3, 321000 
    UNION SELECT 2003, 4, 324000 
    UNION SELECT 2004, 1, 612000 
    UNION SELECT 2004, 2, 524000 
    UNION SELECT 2004, 3, 342000 
    UNION SELECT 2004, 4, 357000 
    UNION SELECT 2005, 1, 734000 
GO 
 
SELECT Y,  
    [1] AS Q1, 
    [2] AS Q2, 
    [3] AS Q3, 
    [4] AS Q4 
FROM 
    (SELECT Y, Q, Sales 
        FROM SalesByQuarter) s PIVOT 
( 
    SUM(Sales) 
    FOR Q IN ([1],[2],[3],[4]) 
) p 
ORDER BY [Y] 
GO 
 
DROP TABLE dbo.SalesByQuarter 
GO

ps. Though you should really try to do it on the client side.

thanks, but sorry the data is not the one i used, maybe see attachment 1.png is the table

where 2.png is the result..it used lengthly UNION ALL

[kevchadders]

There was also another poster trying to do a similar thing here.

(Read first message)
http://www.vbforums.com/showthread.php?t=533390

My first post for it shows how you can do it using a temp table.
http://www.vbforums.com/showpost.php...87&postcount=2

is this the sort of thing you want?

[erickwidya]

thanks,

i think it can done that way but it will involving many update

i guess it need to be done the 'lengthly' way