Column to Row query

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,

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.

Quote:

---

Originally Posted by 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.

---

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

is crystal report's Cross Tab report will help?

Quote:

---

Originally Posted by riteshjain1982

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

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

Code:

---

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.

Quote:

---

Originally Posted by 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

Code:

---

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

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?

thanks,

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

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