SQL Query - convert multiple rows and columns into single row
-
Hi All,
I have this query
SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10
Your help is greatly appreciated!..
Thanks!
-
Hi Tomyum,
I'm sure we can help out, but it will be easier with sample data and tables.
Please see the first link in my signature block for the best way to do this.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Hello Stuart,
Thanks for you reply.
I apologize for posting a query that does not follow the standards.
Anyway, here it is again.
I have this example table
CREATE TABLE TEST(
Code nvarchar (2),
Amt numeric(18, 0)
)
INSERT INTO TEST VALUES('09',1000)
INSERT INTO TEST VALUES('09',12000)
INSERT INTO TEST VALUES('09',1300)
INSERT INTO TEST VALUES('09',1400)
INSERT INTO TEST VALUES('09',1500)
INSERT INTO TEST VALUES('10',2000)
INSERT INTO TEST VALUES('10',2100)
INSERT INTO TEST VALUES('10',2200)
INSERT INTO TEST VALUES('10',2300)
INSERT INTO TEST VALUES('10',2400)
INSERT INTO TEST VALUES('10',2500)
INSERT INTO TEST VALUES('11',3000)
INSERT INTO TEST VALUES('11',3100)
INSERT INTO TEST VALUES('11',3200)
INSERT INTO TEST VALUES('11',3300)
INSERT INTO TEST VALUES('11',3400)
INSERT INTO TEST VALUES('11',3500)
INSERT INTO TEST VALUES('12',4000)
INSERT INTO TEST VALUES('12',4100)
INSERT INTO TEST VALUES('12',4200)
INSERT INTO TEST VALUES('12',4300)
INSERT INTO TEST VALUES('12',4400)
INSERT INTO TEST VALUES('12',4500)
And I have this query and the result
SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END [Col1]
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END [Col2]
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END [Col3]
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END [Col4]
FROM TEST WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code
Result
TypeCol1Col2Col3Col4
Type172.0000000.0000000.0000000.000000
Type0.000000135.0000000.0000000.000000
Type0.0000000.000000195.0000000.000000
Type0.0000000.0000000.000000255.000000
How can I modify my query to have this output below?
Type Col1 Col2 Col3 Col4
Type 172.00 135.00 195.00 255
Thanks!
-
Hi,
Thanks for supplying the data - it makes life easier for anyone answering your question - and it means you will get an answer quicker.
One (quick and dirty) way of doing it would be:-
WITH TheData (TYPE,Col1,Col2,Col3,Col4) as
(SELECT
'Type'[Type]
,(CASE WHEN code='09' THEN sum(Amt/100) ELSE 0 END) [Col1]
,(CASE WHEN code='10' THEN sum(Amt/100) ELSE 0 END) [Col2]
,(CASE WHEN code='11' THEN sum(Amt/100) ELSE 0 END) [Col3]
,(CASE WHEN code='12' THEN sum(Amt/100) ELSE 0 END) [Col4]
FROM TEST
GROUP BY Code)
SELECT Type,
SUM(col1) col1,
SUM(col2) col2,
SUM(col3) col3,
SUM(col4) col4
FROM TheData
GROUP BY type
It is possible that some one will come up with a more elegant method then mine - so don't rush to use any solution you see here. Also make sure you understand the method you choose as you will be supporting it going forwards :w00t:
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
My solution as follows.
SELECT 'Type' Type
, col1 = [09]
, col2 = [10]
, col3 = [11]
, col4 = [12]
FROM
(
select *
from
(
select code,SUM(Amt/100) sum1 FROM TEST
WHERE (Code BETWEEN'09' AND '12')group by Code)t
pivot
(min(sum1) for code in([09],[10],[11],[12])
)pvt
) P
Stuart Davies, I have posted mine just to give a different solution. No other intention
-
how about this one
DECLARE @test-2 AS TABLE (Code nvarchar (2),Amt numeric(18, 0))
INSERT INTO @test-2
VALUES
('09',1000), ('09',12000), ('09',1300), ('09',1400), ('09',1500),
('10',2000), ('10',2100), ('10',2200), ('10',2300), ('10',2400), ('10',2500),
('11',3000), ('11',3100), ('11',3200), ('11',3300), ('11',3400), ('11',3500),
('12',4000), ('12',4100), ('12',4200), ('12',4300), ('12',4400), ('12',4500);
WITH base As(
SELECT
CASE WHEN code='09' THEN Amt/100 ELSE 0 END [Col1]
,CASE WHEN code='10' THEN Amt/100 ELSE 0 END [Col2]
,CASE WHEN code='11' THEN Amt/100 ELSE 0 END [Col3]
,CASE WHEN code='12' THEN Amt/100 ELSE 0 END [Col4]
FROM @test-2
)
SELECT 'Type' AS TYPE, '09'=SUM(col1),'10'=SUM(col2),'11'=SUM(col3),'12'=SUM(col4)
FROM base
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (4/25/2014)
how about this oneDECLARE @test-2 AS TABLE (Code nvarchar (2),Amt numeric(18, 0))
INSERT INTO @test-2
VALUES
('09',1000), ('09',12000), ('09',1300), ('09',1400), ('09',1500),
('10',2000), ('10',2100), ('10',2200), ('10',2300), ('10',2400), ('10',2500),
('11',3000), ('11',3100), ('11',3200), ('11',3300), ('11',3400), ('11',3500),
('12',4000), ('12',4100), ('12',4200), ('12',4300), ('12',4400), ('12',4500);
WITH base As(
SELECT
CASE WHEN code='09' THEN Amt/100 ELSE 0 END [Col1]
,CASE WHEN code='10' THEN Amt/100 ELSE 0 END [Col2]
,CASE WHEN code='11' THEN Amt/100 ELSE 0 END [Col3]
,CASE WHEN code='12' THEN Amt/100 ELSE 0 END [Col4]
FROM @test-2
)
SELECT 'Type' AS TYPE, '09'=SUM(col1),'10'=SUM(col2),'11'=SUM(col3),'12'=SUM(col4)
FROM base
this is same as Stuart Davies's solution, rite?
-
Hi Stuart,
Thanks for your solution. I will try and study this one.
-
pmadhavapeddi22 (4/25/2014)
My solution as follows.SELECT 'Type' Type
, col1 = [09]
, col2 = [10]
, col3 = [11]
, col4 = [12]
FROM
(
select *
from
(
select code,SUM(Amt/100) sum1 FROM TEST
WHERE (Code BETWEEN'09' AND '12')group by Code)t
pivot
(min(sum1) for code in([09],[10],[11],[12])
)pvt
) P
Stuart Davies, I have posted mine just to give a different solution. No other intention
Hello pmadhavapeddi22,
Thanks for posting your solution. Greatly appreciated.
-
Hi Thava,
Thanks for sharing your thoughts on this query. I will try this one.
-
Thanks for the update.
Irrespective of which solution you use, test it on your system. All of the solutions work against the 10 or so rows provided, but what's the hit when you run them against 100 rows 10000, 100000000000 ?
We can only guess what setup you have, please check and also understand what you implement.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Stuart Davies (4/25/2014)
Thanks for the update.Irrespective of which solution you use, test it on your system. All of the solutions work against the 10 or so rows provided, but what's the hit when you run them against 100 rows 10000, 100000000000 ?
We can only guess what setup you have, please check and also understand what you implement.
Thanks for your advise.
Actually, the table that I want to run that query has almost 5,000 rows. I need to study it first on how to go about it.
-
hi there, it is almost same with Stuart Davies's but the logic is different
my solution is avoid the summation two times the other solution using two times grouping and summing
if see the execution plan you are able to see it clearly
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
SELECT
'Type'[Type]
,SUM(CASE WHEN code='09' THEN Amt/100 ELSE 0 END) AS [Col1]
,SUM(CASE WHEN code='10' THEN Amt/100 ELSE 0 END) AS [Col2]
,SUM(CASE WHEN code='11' THEN Amt/100 ELSE 0 END) AS [Col3]
,SUM(CASE WHEN code='12' THEN Amt/100 ELSE 0 END) AS [Col4]
FROM TEST WHERE (Code BETWEEN '09' AND '12')
Far away is close at hand in the images of elsewhere.
Anon. -
David Burrows (4/25/2014)
SELECT
'Type'[Type]
,SUM(CASE WHEN code='09' THEN Amt/100 ELSE 0 END) AS [Col1]
,SUM(CASE WHEN code='10' THEN Amt/100 ELSE 0 END) AS [Col2]
,SUM(CASE WHEN code='11' THEN Amt/100 ELSE 0 END) AS [Col3]
,SUM(CASE WHEN code='12' THEN Amt/100 ELSE 0 END) AS [Col4]
FROM TEST WHERE (Code BETWEEN '09' AND '12')
Suspect we have a winner!
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply