September 17, 2014 at 11:13 pm
Hi All,
I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.
Sample data:
create table t1 (id int, Dept char (1), Total int);
insert t1
select 1, 'A', 100
union
select 2, 'B', 120
union
select 3, 'C', 140
union
select 4, 'D', 150;
How do I use LEFT OUTER JOIN to produce result similar to the below?
SELECT 'Total' AS Dept,
[A], , [C], [D]
from
(
select Dept, Total from t1
) as source pivot
(sum([Total]) for
Dept in ([A], , [C], [D])
) as p
This is a piece of cake for you guys but not for me as I am not a coder.
Regards,
September 17, 2014 at 11:57 pm
Here are two snips, should help get you passed this hurdle
😎
Snip 1, using logical operators instead of a case statement, works in SQLite but not in TSQL
select
1 AS Group_ID
,T.id
,T.Dept
,T.Total
,TX.col_key
,(T.id = TX.col_key) * T.Total
from t1 T
cross join
(select distinct id as col_key from t1) as TX;
Results
# |1|id|Dept|Total|col_key|(T.id = TX.col_key) * T.Total
--+-+--+----+-----+-------+-----------------------------
1 |1|1 |A |100 |1 |100
2 |1|1 |A |100 |2 |0
3 |1|1 |A |100 |3 |0
4 |1|1 |A |100 |4 |0
5 |1|2 |B |120 |1 |0
6 |1|2 |B |120 |2 |120
7 |1|2 |B |120 |3 |0
8 |1|2 |B |120 |4 |0
9 |1|3 |C |140 |1 |0
10|1|3 |C |140 |2 |0
11|1|3 |C |140 |3 |140
12|1|3 |C |140 |4 |0
13|1|4 |D |150 |1 |0
14|1|4 |D |150 |2 |0
15|1|4 |D |150 |3 |0
16|1|4 |D |150 |4 |150
Snip 2, cross tab set using pre-set column ordinals as key. Note that the group_id is fixed as is the group by column, in a real set this would be a group key column.
select
1 AS Group_ID
,sum((T.id = 1) * T.Total) AS A
,sum((T.id = 2) * T.Total) AS B
,sum((T.id = 3) * T.Total) AS C
,sum((T.id = 4) * T.Total) AS D
from t1 T
cross join
(select distinct id as col_key from t1) as TX
group by 1;
Results
#|1|sum((T.id = 1) * T.Total)|sum((T.id = 2) * T.Total)|sum((T.id = 3) * T.Total)|sum((T.id = 4) * T.Total)
-+-+-------------------------+-------------------------+-------------------------+-------------------------
1|1|400 |480 |560 |600
September 18, 2014 at 11:35 am
Hi Eirikur,
How do I get the result like below without using pivot? Thx.
------------------------------------
Dept | A | B | C | D |
------------------------------------
Total | 100 | 120 | 140 | 150 |
------------------------------------
September 18, 2014 at 11:52 am
Something like this?
SELECT 'Total' AS Dept,
SUM(CASE WHEN dept = 'A' THEN Total ELSE 0 END) AS A,
SUM(CASE WHEN dept = 'B' THEN Total ELSE 0 END) AS B,
SUM(CASE WHEN dept = 'C' THEN Total ELSE 0 END) AS C,
SUM(CASE WHEN dept = 'D' THEN Total ELSE 0 END) AS D
FROM t1;
September 18, 2014 at 12:07 pm
Oops, used the wrong code:-P Here is the correct version, in fact the same thing Luis posted, but in SQLite dialect
😎
select
1 AS Group_ID
,sum((T.id = 1) * T.Total) AS A
,sum((T.id = 2) * T.Total) AS B
,sum((T.id = 3) * T.Total) AS C
,sum((T.id = 4) * T.Total) AS D
from t1 T
group by 1;
Results
#|1|sum((T.id = 1) * T.Total)|sum((T.id = 2) * T.Total)|sum((T.id = 3) * T.Total)|sum((T.id = 4) * T.Total)
-+-+-------------------------+-------------------------+-------------------------+-------------------------
1|1|100 |120 |140 |150
September 18, 2014 at 12:11 pm
Eirikur Eiriksson (9/18/2014)
Oops, used the wrong code:-P Here is the correct version, in fact the same thing Luis posted, but in SQLite dialect😎
I tested it on sqlfiddle.com and it worked correctly for SQLite. The sample data didn't work, though.
September 18, 2014 at 12:32 pm
Luis Cazares (9/18/2014)
Eirikur Eiriksson (9/18/2014)
Oops, used the wrong code:-P Here is the correct version, in fact the same thing Luis posted, but in SQLite dialect😎
I tested it on sqlfiddle.com and it worked correctly for SQLite. The sample data didn't work, though.
Another oops for me, it's been one of those days where everything goes more or less sour:w00t:
Luis, your code works fine in SQLite3, best thing for testing it is to use SQLiteStudio, http://sqlitestudio.pl/ single binary and no install, I've used it a lot through the years for teaching (non vendor specific) SQL as it works on most platforms.
😎
September 21, 2014 at 9:53 pm
Thanks Luis and Eirikir. I really appreciated your help. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply