December 15, 2014 at 5:51 pm
Here's the resultset:
Main
CC-09-00032-D
CC-09-00113-A
PR-10-01004-2
Expected result:
P C
PR-10-01004-2 CC-09-00032-D
CC-09-00113-A
What I need is split the data into two columns if data in column Main starts with 'PR-' then output result to column P and if it starts with 'CC-' then to column C (the output needs to be in one table).
December 15, 2014 at 5:58 pm
How do you define that PR-10-01004-2 relates with CC-09-00032-D and not with CC-09-00113-A? Or is that irrelevant? Do you need some kind of order whatsoever?
December 15, 2014 at 6:00 pm
It is irrelevant.
All I need is if it starts with P in one column and if with C then other column.
December 15, 2014 at 6:29 pm
I hope that this is some kind of weird reporting requirement as it shouldn't be used to store data unrelated in the same row of a table.
WITH SampleData(Main) AS(
SELECT Main
FROM (VALUES('CC-09-00032-D'),('CC-09-00113-A'),('PR-10-01004-2'))a(Main)
),
CTE AS(
SELECT CASE WHEN Main LIKE 'PR%' THEN Main END P,
CASE WHEN Main LIKE 'CC%' THEN Main END C,
ROW_NUMBER() OVER(PARTITION BY LEFT(Main,2) ORDER BY Main) rn
FROM SampleData
)
SELECT MAX( P) P,
MAX(C) C
FROM CTE
GROUP BY rn;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply