May 14, 2014 at 3:47 pm
Hello everyone
I have a client with a fairly simple table as illustrated in my sample code. Their interesting requirement is that the records would be listed horizontally, end-to-end as I hope you can visualize based on what I'm providing here. Two other characteristics/specs of note, 1) there could be as few as two records end-to-end in a row or as many as 100 and 2) of course the column headings would have to be unique.
I have pretty much decided I have to talk them out of this (the only reason for this is that they want related records in a single row) but I thought I would run it by some finer minds than mine so see if there is a proper technique I may have overlooked.
As always, thank you in advance.
create table #tmpEndToEnd
(
ID int identity,
ClientID char(10),
Agency varchar(20),
Clinician varchar(20),
Goal varchar(50)
)
insert #tmpEndToEnd
select 'KJHGYUIKJH','BUDS CLUB','SARAH','FEEL BETTER FOREVER' UNION
select 'FDSAADFFSF','BOYS TOWN','JIM','GET A NEW CAREER' UNION
select 'FHGJFJHFJJ','OUR PLACE','NORA','FIND MY REAREND IN THE DARK' UNION
select 'TUYIRTYUYU','DORYS TAVERN','NOAH','ACQUIRE A SPIRTUTAL NATURE' UNION
select 'ASDFFWERRR','CLOWN TOWN','EMMET','BE A GOOD SMARITAN'
-- Variable number of rows. Could be over 100
-- DESIRED RESULTS, VERTICAL RECORDS LAID OUT HORIZONATALLY END TO END
ClientID1, Agency1,Clinician1, Goal1, ClientID2, Agency2,Clinician2, Goal2, ClientID3...
KJHGYUIKJH,BUDS CLUB,SARAH,FEEL BETTER FOREVER,FDSAADFFSF,BOYS TOWN,JIM,GET A NEW CAREER,FHGJFJHFJJ....
May 14, 2014 at 4:24 pm
You might need to create a dynamic cross tab. I'm not sure if you will face problems with some limitations on columns or strings.
Read about them in here: http://qa.sqlservercentral.com/articles/Crosstab/65048/
May 15, 2014 at 7:15 am
Thank you, Luis. You are the only one to respond so far so I am pretty sure I have posed a "toughie" to the team. Yes, a dynamic solution is the only possible one and heaven forgive me if that turns out to be a loop. If I can't get the user to change their minds about the design and eventually have a solution, I'll post it.
May 15, 2014 at 7:34 am
jshahan (5/15/2014)
Thank you, Luis. You are the only one to respond so far so I am pretty sure I have posed a "toughie" to the team. Yes, a dynamic solution is the only possible one and heaven forgive me if that turns out to be a loop. If I can't get the user to change their minds about the design and eventually have a solution, I'll post it.
You definitely don't need a loop for this. What you need is a dynamic cross tab as Luis suggested. Read the article and apply the concepts to your data and you will get the desired results with no loops.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply