Creating View without duplicates, hope this not a dumb question lol
-
SELECT 'Hello World!'
Hey guys, I hope this isn't a stupid question. I feel like sometimes I do ask them :(.
Well I have a table that's not really normalized. What I want to do is create a view that will combine the duplicate persons and add all their ate values without duplicating the ate values. Please refer to the pictures below:
where i'm stuck at:
turn results into this:
-
I'm sure you could write some pretty ugly sql to get this done, but just how married to this table schema are you? The design of this table makes it very difficult to do what you want and when you get it figured out, it will be very inefficient. I would HIGHLY recommend changing the design of the tables.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
DCPeterson (8/3/2009)
I'm sure you could write some pretty ugly sql to get this done, but just how married to this table schema are you? The design of this table makes it very difficult to do what you want and when you get it figured out, it will be very inefficient. I would HIGHLY recommend changing the design of the tables.pretty married but I'm thinking the only way to get this done is to redo the the table schema by breaking it down to normalized tables. like a persons table and then a food table and a table in between to link them.
Is there a way to solve this problem using temp tables? I just now thought of that.
-
I agree, you really should look at normalizing the table. Barring that, however, there is a solution.
Please see the attached files. (posting from work is hit or miss, and uploading attachments seems easiest)
-
If you are really tied to that table design, post the ddl and I'll see what I can come up with.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
DCPeterson (8/3/2009)
If you are really tied to that table design, post the ddl and I'll see what I can come up with.Here it is. Thanks. I'm looking at what Lynn Created too
-
Here is my delimited split dunction for nvarchar values.
-
I didn't see that Lynn had posted a solution, but in looking at it, that's pretty much the direction I was headed anyway...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
DCPeterson (8/3/2009)
I didn't see that Lynn had posted a solution, but in looking at it, that's pretty much the direction I was headed anyway...yeah what he did was really cool. I got busy at work so I stopped looking at it. But I can't wait to get home and dissect it.
I think I have much to learn.
-
I would also recommend that you go with Lynn's solution - this kind of un-normalised view should only really be used for display. In the interests of completeness, if anyone is interested, the solution to your original question is as follows:
SELECTt1.contactid, t1.fname, t1.lname,
STUFF((
SELECT ', '+t2.ate
FROM petertest t2
WHERE t1.contactid = t2.contactid FOR XML PATH('')),1,2,'')
FROM petertest t1
GROUP BY t1.contactid, t1.fname, t1.lname
-
Allister Reid (8/3/2009)
I would also recommend that you go with Lynn's solution - this kind of un-normalised view should only really be used for display. In the interests of completeness, if anyone is interested, the solution to your original question is as follows:SELECTt1.contactid, t1.fname, t1.lname,
STUFF((
SELECT ', '+t2.ate
FROM petertest t2
WHERE t1.contactid = t2.contactid FOR XML PATH('')),1,2,'')
FROM petertest t1
GROUP BY t1.contactid, t1.fname, t1.lname
But that wouldn't eliminate the duplicate foods between multiple records for each individual will it?
That was the original request.
-
Heh he, you're quite right :blush:, guess he'd need to normalise as per your solution, take a distinct on the resultant item table, then concatenate with XML FOR PATH method 😉
///Edit - guess that's even more reason to have the data in normal form in the first place - more flexible
-
For more information on how the method Lynn used actually works, please see the following article...
http://qa.sqlservercentral.com/articles/T-SQL/62867/
In the code Lynn posted, he used a really cool CTE similar to what Itzek uses but just a wee bit faster (I've tested it). That CTE is used in place of a Tally table (I call it a cteTally in my code) and it's a very fast solution just like when you use a Tally table against splits on things less 8001 bytes. It also eliminates the need to keep a Tally table and assures you that you won't overrun the max value of a Tally table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply