November 4, 2014 at 7:08 pm
Hi,
If I have a table like this:
idval1val2
----------------------------
1red NULL
1blueNULL
1greenround
1NULLsquare
where val1 and val2 are unrelated except that both are tied to the same id, how can I query to produce these results:
idval1val2
----------------------------
1redround
1bluesquare
1greenNULL
Joining derived tables on id repeats all combinations of val1 and val2.
Ultimately these results need to be displayed via SSRS, so if anyone knows an easier way to handle it there, that would work too. I am interested to know how this could be done purely with T-SQL though, ideally without pivot or cursor/looping.
Thanks!
November 5, 2014 at 12:01 am
I am sorry, but I don't see the logics behind the results you are expecting. Could you explain it a little 🙂
Roland
November 5, 2014 at 5:51 am
I agree with Roland, the logic makes no sense and have no idea what you are trying to do. Seems like very poor table design to me.
November 5, 2014 at 6:01 am
I don't see the logic in generating the results either. Could you please explain how you get from the source data to the report? It'll make it easier for people to help you with your problem.
November 5, 2014 at 10:02 am
It seems that you have a poor design and that's making everyone confused. You should review what's going on here.
That said, this might help you:
CREATE TABLE SampleData(
idint,
val1varchar(50),
val2 varchar(50));
INSERT INTO SampleData
VALUES
(1,'red', NULL),
(1,'blue',NULL),
(1,'green','round'),
(1,NULL,'square');
WITH val1s AS(
SELECT id,
val1,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY val1 DESC) rn
FROM SampleData
WHERE val1 IS NOT NULL
),
val2s AS(
SELECT id,
val2,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY val2 DESC) rn
FROM SampleData
WHERE val2 IS NOT NULL
)
SELECT ISNULL( v1.id, v2.id) id,
v1.val1,
v2.val2
FROM val1s v1
FULL
JOIN val2s v2 ON v1.id = v2.id AND v1.rn = v2.rn
GO
DROP TABLE SampleData
Next time, please post sample data and expected results the same way I did here to prevent us from having to do additional work (we're not paid for helping).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply