One PIVOT-ing approach is per-item selects when you don't know the subject
value - this is an exact version of your question:
CREATE TABLE `temptest` (
`ID` INTEGER PRIMARY KEY,
`Col1` TEXT,
`Col2` TEXT,
`Col3` TEXT,
`Value` TEXT
);
INSERT INTO `temptest` (`Col1`, `Col2`, `Col3`, `Value`) VALUES
('a', NULL, NULL, 'X'),
(NULL, 'a', NULL, 'Y'),
(NULL, NULL, 'a', 'Z'),
('b', NULL, NULL, 'A'),
(NULL, 'b', NULL, 'B'),
(NULL, NULL, 'b', 'C');
So the full table is now this:
ID Col1 Col2 Col3 Value
--- ------ ------ ------ -------
1 a X
2 a Y
3 a Z
4 b A
5 b B
6 b C
And using this query:
SELECT (coalesce(T.Col1,T.Col2,T.Col3)) AS A,
(SELECT Value FROM temptest AS X1 WHERE X1.Col1=coalesce(T.Col1,T.Col2,T.Col3)) AS
"Value",
(SELECT Value FROM temptest AS X1 WHERE X1.Col2=coalesce(T.Col1,T.Col2,T.Col3)) AS
"Value",
(SELECT Value FROM temptest AS X1 WHERE X1.Col3=coalesce(T.Col1,T.Col2,T.Col3)) AS
"Value"
FROM temptest AS T GROUP BY A;
Yields these results:
A Value Value Value
a X Y Z
b A B C
As requested.
You can shorten the SQL by using some "AS" statements etc, and not sure if it will work for whatever big query you have in mind -
The question seems very unspecific though and the table is weird in any SQL handbook's terms, but this gets the required results.
On 2013/12/05 20:15, Hayden Livingston wrote:
I have a table schema such like
ID | Col1 | Col2 | Col3 | Value
1 a null null X
2 null a null Y
3 null null a Z
4 b null null A
5 null b null B
6 null null b C
Right now these are in the same table (they may be different tables in the
near future, but I don't think that impacts this discussion)
I want to "PIVOT" this data such that:
A | Value | Value | Value
a X Y Z
b A B C
Searching on the internet says this can be done in SQLite using CASE
statements? But what happens I don't know the row id values (i.e. a, b,
etc)?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
