|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Statement Help - Concatenate StringHope someone can help me, in a bit of a muddle and trying to find the most efficient solution! Basically I have a table which has two fields (which I am interested in): PersonID, Category. 1, A 1, CE 1, B 1, C 2, B 2, BE What I basically want to do is return a single string for each PersonID with ALL the categories in it. IE: PersonID 1 - "A, CE, B, C" (or better sorted) PersonID 2 - "B, BE" There could be any number of different categories (currently about 17 max, and each person could have any combination of these). I want to link this into part of a query to get other info out of other tables about that person. Anyone got any ideas of the most efficient way of doing this? I thought about using a SP for each Person and looping through all the Categories they have to create a string and returning that - but there must be a more efficient way?? Any (and all) help much appreciated! Regards, Craig Parsons. Hi
I assume you are trying to use dynamic SQL? You may want to look at http://www.sommarskog.se/arrays-in-sql.html If you post DDL, example data and require output (see http://www.aspfaq.com/etiquette.asp?id=5006 ) it may be possibly to produce a working solution, but without that you may be able to get what you require from something like: SELECT A.OtherInfo FROM OtherTable A JOIN Categories C ON A.Category = C.Category WHERE C.PersonId = @PersonId John Show quote "Craig Parsons" <news.po***@cparsons.plus.com> wrote in message news:43078aba$0$97112$ed2619ec@ptn-nntp-reader03.plus.net... > Folks, > > Hope someone can help me, in a bit of a muddle and trying to find the > most efficient solution! Basically I have a table which has two fields > (which I am interested in): > > PersonID, Category. > 1, A > 1, CE > 1, B > 1, C > 2, B > 2, BE > > What I basically want to do is return a single string for each PersonID > with ALL the categories in it. IE: > PersonID 1 - "A, CE, B, C" (or better sorted) > PersonID 2 - "B, BE" > > There could be any number of different categories (currently about 17 > max, and each person could have any combination of these). > > I want to link this into part of a query to get other info out of other > tables about that person. Anyone got any ideas of the most efficient way > of doing this? > > I thought about using a SP for each Person and looping through all the > Categories they have to create a string and returning that - but there > must be a more efficient way?? > > Any (and all) help much appreciated! > > > Regards, > > > > Craig Parsons. > Craig,
I was attempting to do the same thing a few weeks ago and spun my wheels for quite a while on it. I was pointed to the following "solution" a number of times...and it was close but not the entire answer. http://www.aspfaq.com/show.asp?id=2529 Here is what you need to do (and incorporate what is described in the above link). CREATE FUNCTION fnGetCombinedCategories ( @PersonID INT ) RETURNS VARCHAR(1000) AS BEGIN -- Local variables DECLARE @returnword NVARCHAR(1000) select @returnword = isnull(@returnword,'')+ltrim(rtrim(category))+',' from tbltest where personid = @PersonID order by category --Remove last trailing comma set @returnword = left(@returnword,len(@returnword)-1) --Return it RETURN @returnword END Then use the above function in your SELECT, as follows: select distinct personid, dbo.fnGetCombinedCategories(personid) from tbltest You need to specify DISTINCT because all those with the same personID's will have the same comma-delimited list of categories. If desired, you could make the function more versitile by add parameters for the table and column names & then use dynamic SQL. But, this example has all that stuff hard-coded. Good luck, vc Show quote "Craig Parsons" <news.po***@cparsons.plus.com> wrote in message news:43078aba$0$97112$ed2619ec@ptn-nntp-reader03.plus.net... > Folks, > > Hope someone can help me, in a bit of a muddle and trying to find the > most efficient solution! Basically I have a table which has two fields > (which I am interested in): > > PersonID, Category. > 1, A > 1, CE > 1, B > 1, C > 2, B > 2, BE > > What I basically want to do is return a single string for each PersonID > with ALL the categories in it. IE: > PersonID 1 - "A, CE, B, C" (or better sorted) > PersonID 2 - "B, BE" > > There could be any number of different categories (currently about 17 > max, and each person could have any combination of these). > > I want to link this into part of a query to get other info out of other > tables about that person. Anyone got any ideas of the most efficient way of > doing this? > > I thought about using a SP for each Person and looping through all the > Categories they have to create a string and returning that - but there must > be a more efficient way?? > > Any (and all) help much appreciated! > > > Regards, > > > > Craig Parsons. > > Your whole approach is wrong.
What is the first prinicple of a tiered architecture? You format data in the client, never in the database! Do you know what First Normal Form (1NF) is? A column contains only scalar values. And yes, you will write loops and lots of proceudral code, not SQL. Why do you want to return a string for each PersonID? If you want to link
this to get other info out of other tables--in other words, join other tables--then concatenating the categories is absolutely the wrong way to go about this. Better to simply join this table to the other tables as-is. Show quote "Craig Parsons" <news.po***@cparsons.plus.com> wrote in message news:43078aba$0$97112$ed2619ec@ptn-nntp-reader03.plus.net... > Folks, > > Hope someone can help me, in a bit of a muddle and trying to find the > most efficient solution! Basically I have a table which has two fields > (which I am interested in): > > PersonID, Category. > 1, A > 1, CE > 1, B > 1, C > 2, B > 2, BE > > What I basically want to do is return a single string for each PersonID > with ALL the categories in it. IE: > PersonID 1 - "A, CE, B, C" (or better sorted) > PersonID 2 - "B, BE" > > There could be any number of different categories (currently about 17 > max, and each person could have any combination of these). > > I want to link this into part of a query to get other info out of other > tables about that person. Anyone got any ideas of the most efficient way of > doing this? > > I thought about using a SP for each Person and looping through all the > Categories they have to create a string and returning that - but there must > be a more efficient way?? > > Any (and all) help much appreciated! > > > Regards, > > > > Craig Parsons. > > Why? Most likely they have a UI that they want to display it on. A
normalized set takes more space than a comma delimited one. I know whenever I am forced to write a report for a client that often they want to see all of people who worked on the sales team, and adding another line per salesperson could make the report grow very large. Clearly this ought to be a UI job, but that is not always reasonable. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Brian Selzer" <br***@selzer-software.com> wrote in message news:Oo8uTrhpFHA.1416@TK2MSFTNGP09.phx.gbl... > Why do you want to return a string for each PersonID? If you want to link > this to get other info out of other tables--in other words, join other > tables--then concatenating the categories is absolutely the wrong way to > go > about this. Better to simply join this table to the other tables as-is. > > "Craig Parsons" <news.po***@cparsons.plus.com> wrote in message > news:43078aba$0$97112$ed2619ec@ptn-nntp-reader03.plus.net... >> Folks, >> >> Hope someone can help me, in a bit of a muddle and trying to find > the >> most efficient solution! Basically I have a table which has two fields >> (which I am interested in): >> >> PersonID, Category. >> 1, A >> 1, CE >> 1, B >> 1, C >> 2, B >> 2, BE >> >> What I basically want to do is return a single string for each > PersonID >> with ALL the categories in it. IE: >> PersonID 1 - "A, CE, B, C" (or better sorted) >> PersonID 2 - "B, BE" >> >> There could be any number of different categories (currently about 17 >> max, and each person could have any combination of these). >> >> I want to link this into part of a query to get other info out of > other >> tables about that person. Anyone got any ideas of the most efficient way > of >> doing this? >> >> I thought about using a SP for each Person and looping through all >> the >> Categories they have to create a string and returning that - but there > must >> be a more efficient way?? >> >> Any (and all) help much appreciated! >> >> >> Regards, >> >> >> >> Craig Parsons. >> >> > > The original table already has 1 row per category, so concatenating before
joining is just stupid. Perhaps I should have been more clear: Better to simply join this table to the other tables as-is and then pivot the results. Show quote "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message news:eUAAP6opFHA.3552@TK2MSFTNGP10.phx.gbl... > Why? Most likely they have a UI that they want to display it on. A > normalized set takes more space than a comma delimited one. I know whenever > I am forced to write a report for a client that often they want to see all > of people who worked on the sales team, and adding another line per > salesperson could make the report grow very large. > > Clearly this ought to be a UI job, but that is not always reasonable. > > -- > -------------------------------------------------------------------------- -- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > "Arguments are to be avoided: they are always vulgar and often convincing." > (Oscar Wilde) > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:Oo8uTrhpFHA.1416@TK2MSFTNGP09.phx.gbl... > > Why do you want to return a string for each PersonID? If you want to link > > this to get other info out of other tables--in other words, join other > > tables--then concatenating the categories is absolutely the wrong way to > > go > > about this. Better to simply join this table to the other tables as-is. > > > > "Craig Parsons" <news.po***@cparsons.plus.com> wrote in message > > news:43078aba$0$97112$ed2619ec@ptn-nntp-reader03.plus.net... > >> Folks, > >> > >> Hope someone can help me, in a bit of a muddle and trying to find > > the > >> most efficient solution! Basically I have a table which has two fields > >> (which I am interested in): > >> > >> PersonID, Category. > >> 1, A > >> 1, CE > >> 1, B > >> 1, C > >> 2, B > >> 2, BE > >> > >> What I basically want to do is return a single string for each > > PersonID > >> with ALL the categories in it. IE: > >> PersonID 1 - "A, CE, B, C" (or better sorted) > >> PersonID 2 - "B, BE" > >> > >> There could be any number of different categories (currently about 17 > >> max, and each person could have any combination of these). > >> > >> I want to link this into part of a query to get other info out of > > other > >> tables about that person. Anyone got any ideas of the most efficient way > > of > >> doing this? > >> > >> I thought about using a SP for each Person and looping through all > >> the > >> Categories they have to create a string and returning that - but there > > must > >> be a more efficient way?? > >> > >> Any (and all) help much appreciated! > >> > >> > >> Regards, > >> > >> > >> > >> Craig Parsons. > >> > >> > > > > > > Ahh, yes. That is very true. I didn't catch that was what you meant. I
sort of meant that when I said it should be a UI thing :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23exMi0ppFHA.3544@TK2MSFTNGP15.phx.gbl... > The original table already has 1 row per category, so concatenating before > joining is just stupid. > > Perhaps I should have been more clear: Better to simply join this table to > the other tables as-is and then pivot the results. > > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message > news:eUAAP6opFHA.3552@TK2MSFTNGP10.phx.gbl... >> Why? Most likely they have a UI that they want to display it on. A >> normalized set takes more space than a comma delimited one. I know > whenever >> I am forced to write a report for a client that often they want to see >> all >> of people who worked on the sales team, and adding another line per >> salesperson could make the report grow very large. >> >> Clearly this ought to be a UI job, but that is not always reasonable. >> >> -- >> -------------------------------------------------------------------------- > -- >> Louis Davidson - http://spaces.msn.com/members/drsql/ >> SQL Server MVP >> "Arguments are to be avoided: they are always vulgar and often > convincing." >> (Oscar Wilde) >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:Oo8uTrhpFHA.1416@TK2MSFTNGP09.phx.gbl... >> > Why do you want to return a string for each PersonID? If you want to > link >> > this to get other info out of other tables--in other words, join other >> > tables--then concatenating the categories is absolutely the wrong way >> > to >> > go >> > about this. Better to simply join this table to the other tables >> > as-is. >> > >> > "Craig Parsons" <news.po***@cparsons.plus.com> wrote in message >> > news:43078aba$0$97112$ed2619ec@ptn-nntp-reader03.plus.net... >> >> Folks, >> >> >> >> Hope someone can help me, in a bit of a muddle and trying to >> >> find >> > the >> >> most efficient solution! Basically I have a table which has two >> >> fields >> >> (which I am interested in): >> >> >> >> PersonID, Category. >> >> 1, A >> >> 1, CE >> >> 1, B >> >> 1, C >> >> 2, B >> >> 2, BE >> >> >> >> What I basically want to do is return a single string for each >> > PersonID >> >> with ALL the categories in it. IE: >> >> PersonID 1 - "A, CE, B, C" (or better sorted) >> >> PersonID 2 - "B, BE" >> >> >> >> There could be any number of different categories (currently about > 17 >> >> max, and each person could have any combination of these). >> >> >> >> I want to link this into part of a query to get other info out of >> > other >> >> tables about that person. Anyone got any ideas of the most efficient > way >> > of >> >> doing this? >> >> >> >> I thought about using a SP for each Person and looping through all >> >> the >> >> Categories they have to create a string and returning that - but there >> > must >> >> be a more efficient way?? >> >> >> >> Any (and all) help much appreciated! >> >> >> >> >> >> Regards, >> >> >> >> >> >> >> >> Craig Parsons. >> >> >> >> >> > >> > >> >> > > Although formatting data should be done on the client (in the application
layer), take a look at this: http://milambda.blogspot.com/2005/07/return-related-values-as-array.html ML  
Other interesting topics
|
|||||||||||||||||||||||
