Forum Home
Press F1
Thread ID: 119657	2011-08-03 03:45:00	SQL view combining records	Mike (15)	Press F1

Post ID	Timestamp	Content	User
1220641	2011-08-03 03:45:00	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.	Mike (15)
1220642	2011-08-03 08:24:00	Pivot Tables would be required, since you could have an infinite number of columns. msdn.microsoft.com	somebody (208)
1220643	2011-08-03 09:50:00	The most columns I'd need would be 4. Mike.	Mike (15)
1220644	2011-08-03 10:24:00	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 www.simple-talk.com	somebody (208)
1220645	2011-08-03 22:05:00	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 (15)
1220646	2011-08-03 22:07:00	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.	Mike (15)
1220647	2011-08-04 08:12:00	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: www.simple-talk.com - it won't give you exactly what you're after, but is a starting point.	somebody (208)
1220648	2011-08-04 08:56:00	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 (15)
1220649	2011-08-04 22:32:00	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.	Mike (15)
1220650	2011-08-05 09:08:00	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.	nofam (9009)
1 2