November 5, 2012 at 1:56 pm
Hi all,
My data looks like this.
CREATE TABLE pivot_test (
id NUMBER,
START_TIME Date,
Group VARCHAR2(5),
);
INSERT INTO pivot_test VALUES (1, '02-Oct-2012', A);
INSERT INTO pivot_test VALUES (1, '03-Oct-2012', B);
INSERT INTO pivot_test VALUES (1, '04-Oct-2012', C);
INSERT INTO pivot_test VALUES (1, '05-Oct-2012', D);
INSERT INTO pivot_test VALUES (2, '01-Oct-2012', A);
INSERT INTO pivot_test VALUES (3, '02-Oct-2012', B);
INSERT INTO pivot_test VALUES (3, '04-Oct-2012', C);
INSERT INTO pivot_test VALUES (4, '05-Oct-2012', 'D');
INSERT INTO pivot_test VALUES (4, '07-Oct-2012', 'A');
COMMIT;
I am trying to combine all the groups together depending on the start_time into a result like this:
ID First Group Second Group Third Group Fourth Group
1 A B C D
2 A B NULL NULL
3 B C NULL NULL
4 D A NULL NULL
Can anyone help me please? I am guessing it's a pivot table solution?
Thanks,
Alan
November 5, 2012 at 2:09 pm
It wasn't completely clear what you needed but the sample data and expected results helped a lot.
I guess there's an error with the second group on id 2, but this is what I understood.
You need to group by occurrence, ordered by date. To do this, you need to have a real thing to group the rows.
Here's the sample code
--CREATE TABLE pivot_test (
--id int,
--START_TIME Date,
--Group_Name VARCHAR(5)
--);
--INSERT INTO pivot_test VALUES (1, '02-Oct-2012', 'A');
--INSERT INTO pivot_test VALUES (1, '03-Oct-2012', 'B');
--INSERT INTO pivot_test VALUES (1, '04-Oct-2012', 'C');
--INSERT INTO pivot_test VALUES (1, '05-Oct-2012', 'D');
--INSERT INTO pivot_test VALUES (2, '01-Oct-2012', 'A');
--INSERT INTO pivot_test VALUES (3, '02-Oct-2012', 'B');
--INSERT INTO pivot_test VALUES (3, '04-Oct-2012', 'C');
--INSERT INTO pivot_test VALUES (4, '05-Oct-2012', 'D');
--INSERT INTO pivot_test VALUES (4, '07-Oct-2012', 'A');
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY id ORDER BY START_TIME) rn
FROM pivot_test)
SELECT id,
MAX( CASE WHEN rn = 1 THEN group_name END) AS first_group,
MAX( CASE WHEN rn = 2 THEN group_name END) AS second_group,
MAX( CASE WHEN rn = 3 THEN group_name END) AS third_group,
MAX( CASE WHEN rn = 4 THEN group_name END) AS fourth_group
FROM CTE
GROUP BY id
Your code had some things that weren't exactly T-SQL, maybe you took the code from another RDBMS. Be careful, if you're not working on SQL Server, I'm not sure that this will work.
November 5, 2012 at 3:31 pm
WITH CTE AS(
SELECT ID, Group_Name, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY START_TIME) rn
FROM pivot_test)
SELECT ID, START_TIME,
MAX( CASE WHEN rn = '1' THEN Group_Name END) AS first_group,
MAX( CASE WHEN rn = '2' THEN Group_Name END) AS second_group,
MAX( CASE WHEN rn = '3' THEN Group_Name END) AS third_group,
MAX( CASE WHEN rn = '4' THEN Group_Name END) AS fourth_group
FROM CTE
WHERE ID = 1
GROUP BY ID, Group_Name
I have this and it is working..partially.
It gives me this result:
ID Group_Name FIRST_GROUP SECOND_GROUP THIRD_GROUP FOURTH_GROUP
1 A A
1 B B
1 C C
1 D D
Any clue? 🙁 Need to group those into 1 row....
November 5, 2012 at 3:50 pm
There are many problems with your query:
1. You didn't include start_time in your CTE definition. This should show an error.
2. You're comparing strings against ints. This is useless and it creates an implicit conversion.
3. You added an extra column in the GROUP BY. This is what's giving you the wrong results.
Read this article about cross tabs: http://www.sqlservercentral.com/articles/T-SQL/63681/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply