PDA

View Full Version : SQL view combining records



Mike
03-08-2011, 02:45 PM
I am wanting to create a SQL view that takes similar records (based on the first 5 characters of a 12 character ID) and if the value in a specific field is different, adds that field to the end of the view (ie if it's the same, then the value only appears once.)

Is that possible?


SHORTID, RECORDID, MODEL
MK001, MK001-3100MK, CP1234-321MT
MK001, MK001-3200MK, CP1234-321MT
MK002, MK002-3100MK, CP1234-321MT
MK002, MK002-3200MK, CP4321-321MTso I'd get two records

SHORTID, MODEL, MODEL2
MK001, CP1234-321MT,
MK002, CP1234-321MT, CP4321-321MTor something like that. I think the most I've got is 3 (same shortID, different model). Most of the time the model is the same.

Does that all make sense? using Microsoft SQL.

Cheers,
Mike.

somebody
03-08-2011, 07:24 PM
Pivot Tables would be required, since you could have an infinite number of columns.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Mike
03-08-2011, 08:50 PM
The most columns I'd need would be 4.

Mike.

somebody
03-08-2011, 09:24 PM
The most columns I'd need would be 4.

Mike.

What's stopping this from making it into your dataset?


SHORTID, RECORDID, MODEL
MK002, MK002-3100MK, CP1234-321MT
MK002, MK002-3200MK, CP4321-321MT
MK002, MK002-3200MK, CP4320-321MT
MK002, MK002-3200MK, CP4322-321MT
MK002, MK002-3200MK, CP4323-321MT
MK002, MK002-3200MK, CP4324-321MT



Edit: Either way you should still use pivottables. Check out http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Mike
04-08-2011, 09:05 AM
What's stopping this from making it into your dataset?Well just the fact that it won't :) Looking through the data I've got now, the most different models I've got for the same shortID is 1 (so 1 extra column) so I'm allowing for possibly another 1 or 2, but very unlikely at this stage.

I've never used Pivots with SQL before - how do they differ from a view, and would I be able to query it the same as a view (I need to see it as if it were a table - same way a view works for me at the moment).

Mike.

Mike
04-08-2011, 09:07 AM
Oh I think I see how the Pivot works. I'm not sure it'll do what I want? I still need the data left-to-right (ShortID, Model1, Model2). Or am I missing something here?

Mike.

somebody
04-08-2011, 07:12 PM
Oh I think I see how the Pivot works. I'm not sure it'll do what I want? I still need the data left-to-right (ShortID, Model1, Model2). Or am I missing something here?

Mike.

No - you're right. I've misunderstood your problem.

I think what you want is doable, I just can't think of the right way to do it at the moment.

Edit: You can try something like: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ - it won't give you exactly what you're after, but is a starting point.

Mike
04-08-2011, 07:56 PM
somebody, I think that might just do it - I hadn't thought of concatenating them all together into one field, but that would probably work for what I want too. I'll give it a go when I'm at work tomorrow, and let you know. :D

Cheers,
Mike.

Mike
05-08-2011, 09:32 AM
Thanks somebody, that's mostly done the trick (it doesn't remove the duplicates).


with Ranked(SHORTID, rnk, MODELNO)
as( SELECT SHORTID, ROW_NUMBER() OVER (PARTITION BY SHORTID
ORDER BY SHORTID), CAST(ModelNO AS VARCHAR(8000))
FROM view_1), AnchorRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM Ranked
WHERE rnk = 1), RecurRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM AnchorRanked
UNION ALL
SELECT Ranked.SHORTID, Ranked.rnk, RecurRanked.ModelNO + ', ' + Ranked.ModelNO
FROM Ranked INNER JOIN
RecurRanked ON Ranked.SHORTID = RecurRanked.SHORTID AND Ranked.rnk = RecurRanked.rnk + 1)
SELECT SHORTID, MAX(ModelNO) AS MODELS
FROM RecurRanked
GROUP BY SHORTID;

of course most of that means nothing to me :)

cheers,
Mike.

nofam
05-08-2011, 08:08 PM
Thanks somebody, that's mostly done the trick (it doesn't remove the duplicates).


with Ranked(SHORTID, rnk, MODELNO)
as( SELECT SHORTID, ROW_NUMBER() OVER (PARTITION BY SHORTID
ORDER BY SHORTID), CAST(ModelNO AS VARCHAR(8000))
FROM view_1), AnchorRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM Ranked
WHERE rnk = 1), RecurRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM AnchorRanked
UNION ALL
SELECT Ranked.SHORTID, Ranked.rnk, RecurRanked.ModelNO + ', ' + Ranked.ModelNO
FROM Ranked INNER JOIN
RecurRanked ON Ranked.SHORTID = RecurRanked.SHORTID AND Ranked.rnk = RecurRanked.rnk + 1)
SELECT SHORTID, MAX(ModelNO) AS MODELS
FROM RecurRanked
GROUP BY SHORTID;

of course most of that means nothing to me :)

cheers,
Mike.

Take a closer look at your GROUP BY clause; that should allow you to remove any duplicates.

Mike
09-08-2011, 09:06 AM
OK well I thought it was all working nicely, but have just discovered a problem. If any of the records has a Null for ModelNo (even if other ModelNo for same ShortID is not Null), the whole concatenated string is Null. Any ideas how I tell the above script to ignore the Null value (or substitute it with an empty "" string)?

Thanks,
Mike.

Mike
09-08-2011, 09:06 AM
Take a closer look at your GROUP BY clause; that should allow you to remove any duplicates.thanks for the suggestion nofam, but I'm afraid all I see is a bunch of words :(

Mike.

Erayd
09-08-2011, 12:54 PM
OK well I thought it was all working nicely, but have just discovered a problem. If any of the records has a Null for ModelNo (even if other ModelNo for same ShortID is not Null), the whole concatenated string is Null. Any ideas how I tell the above script to ignore the Null value (or substitute it with an empty "" string)?

Thanks,
Mike.Use COALESCE - that's pretty much the whole point of this function :). COALESCE returns its first non-null argument.

Mike
09-08-2011, 03:02 PM
Use COALESCE - that's pretty much the whole point of this function :). COALESCE returns its first non-null argument.

Thanks Erayd :)


with Ranked(SHORTID, rnk, MODELNO)
as( SELECT SHORTID, ROW_NUMBER() OVER (PARTITION BY SHORTID
ORDER BY SHORTID), CAST(ModelNO AS VARCHAR(8000))
FROM view_1), AnchorRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM Ranked
WHERE rnk = 1), RecurRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM AnchorRanked
UNION ALL
SELECT Ranked.SHORTID, Ranked.rnk, coalesce(RecurRanked.ModelNO + ', ', '') + Ranked.ModelNO
FROM Ranked INNER JOIN
RecurRanked ON Ranked.SHORTID = RecurRanked.SHORTID AND Ranked.rnk = RecurRanked.rnk + 1)
SELECT SHORTID, MAX(ModelNO) AS MODELS
FROM RecurRanked
GROUP BY SHORTID;

Cheers,
Mike.