May 20, 2009 at 8:56 am
Hi,
I'm a newbie to SQL and was hoping someone can help.
I have a select statement the produces data like the following
ID Name Course
123 Bob ABC
123 Bob BBB
124 John ABC
125 Fred YYY
125 Fred CCC
As you can see some of these are the same person but attend more than one course. What I want to produce is a list of all the people who attend more than one course but I want each line to look like this:
ID Name Course
123 Bob ABC, BBB
125 Fred YYY,CCC
So you can see that Bob's two courses now appear on the one line and his second line no longer appears.
I hope this makes sense and any help would be appreciated.
Thanks.
May 20, 2009 at 9:03 am
This may not answer your question directly, but I think it will help you figure it out, read this blog post: A Variable Length Random String.
I think you will find the code there helpful.
May 20, 2009 at 9:10 am
XML does pretty good with this kind of thing...
Declare @Table Table (id int, Nme varchar(10), Course varchar(10))
Insert Into @Table
Select 123, 'BOB', 'ABC' UNION ALL
Select 123, 'BOB', 'BBB' UNION ALL
Select 124, 'JOHN', 'ABC' UNION ALL
Select 124, 'FRED', 'YYY' UNION ALL
Select 125, 'FRED', 'CCC'
SELECT
t1.ID,
t1.Nme ,
List = substring((SELECT ( ', ' + Course )
FROM @Table t2
WHERE t1.ID = t2.ID
ORDER BY ID
FOR XML PATH( '' )
), 3, 1000 )FROM @Table t1
GROUP BY ID, Nme
May 21, 2009 at 2:31 am
Cheers for the replys, i'll take a look at them.
One last thing - can anyone suggest any good resources to help me learn TSQL?
May 21, 2009 at 3:54 am
A lot of these Microsoft books come with a companion CD rom which also has the e-book on it which is very handy
22 out of 30 people gave it 5/5
May 22, 2009 at 7:50 am
http://www.sql.org is always a good start. Check out the beginner tutorials.
Then come back here once you read your book/do some learning and start reading through articles/forums/blogs/whatever. Once you understand the concepts, this site will teach you more practical use of TSQL than you can shake a stick at.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 22, 2009 at 1:37 pm
Hello,
This is a good post. I'm actually working on the same thing. I've messed around just a bit with transposing columns into rows, but this is something I have a hard time wrapping my brain around, expecially with my longer scripts.
I have this script:
SELECT DISTINCT
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No]
, CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]
, CRM_Orders.PROCESS_TYPE
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
In my result set I get three lines with the same part number but unique [Role]s.
This line is my concatenated field, CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]
and this is where it's calling the unique values, CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
Here is an example:
Part NoRole
102031Exec. Service Employee, Jeff A.
102031Service Employee Group, Global Call Center
102031Sold-To Party, McLaren Center
102503Exec. Service Employee, Jeff A.
102503Service Employee Group, Global Call Center
102503Sold-To Party, Center
5-903-61587Exec. Service Employee, Jeff A.
5-903-61587Service Employee Group, Global Call Center
5-903-61587Sold-To Party, Center
Now I'm wondering if it's possible to have each Role in a column so only one part number line is displayed, but leave everything else as is.
I don't know if this can be done or not?
Thank you for any help anyone can provide!!! I'm a real newbie at this, so I need things spelled out so my simpleton mind can understand!
Michelle :unsure:
May 23, 2009 at 8:18 am
This is something I have thought about myself, from time to time, and am interested to see if it is possible in sql. Pretty green myself, but it seems like you would need to do some kind of PIVOT on the data. However, problem is you gotta know what columns you want in your output before you can perform the PIVOT query.
This means you are going to have to dynamically generate your sql, here is an article on this subject: http://www.sqlservercentral.com/articles/cross+tab/65048/
However, this kind of output sounds like something for display (user interface or reporting) so why not just do it in the application?
I would be interested in what others think on this one.
Here is the code, if anyone wants to post any sql solutions.
create table #PivotExample (PartNo varchar(12),RoleDesc varchar(64))
insert into #PivotExample
select '102031', 'Exec. Service Employee, Jeff A.'
union all
select '102031', 'Service Employee Group, Global Call Center'
union all
select '102031', 'Sold-To Party, McLaren Center'
union all
select '102503', 'Exec. Service Employee, Jeff A.'
union all
select '102503', 'Service Employee Group, Global Call Center'
union all
select '102503', 'Sold-To Party, Center'
union all
select '5-903-61587', 'Exec. Service Employee, Jeff A.'
union all
select '5-903-61587', 'Service Employee Group, Global Call Center'
union all
select '5-903-61587', 'Sold-To Party, Center'
Allister
May 23, 2009 at 11:15 am
allister.reid (5/23/2009)
However, this kind of output sounds like something for display (user interface or reporting) so why not just do it in the application?I would be interested in what others think on this one.
Here is the code, if anyone wants to post any sql solutions.
I'm probably stating the obvious so please, no one take offense to the following...
First of all, having mixed data separated by a comma in the RoleDesc column is a really, really bad idea. If the GUI can make that level of separation, then the Role and Desc should be stored in separate columns to make life a whole lot easier.
Second, this in a "Name Value Pair" (NVP) table and although they allow for great flexibility on the part of users and the ability to add another named "column" just by adding it, they are a bit of a pain to use especially since they usually end up requiring some form of "pivoting" code just to produce a simple normalized result set for use in normal SQL.
Third, since the purpose of such a table is to allow everyone to create whatever new "column" they want, there's no chance of preventing people from bad naming or "near" naming where two people call the same thing by different names.
Lastly, I agree... IF you have a GUI AND you don't really need to access the data from SQL Server itself then, Yes, the GUI would be the place to do this. However, it's a very rare thing that once an NVP table like this is constructed that no one would have to use the data it contains as if it were in a normalized table. And Lord help us all if someone types a comma into either side of the RoleDesc column!
Unless other constraints come into play, the use of free-form NVP's are one of the worst things you can do to a perfectly good database.
Ok, I'll get off the soap-box now. 😛
I've included the original test data creation code in the solution below. As always, the details are in the comments. Basically, the code will allow you to add any and all "columns" to the NVP and produce the data in an unpivoted fashion without knowing how many columns there may be ahead of time.
[font="Courier New"]--===== Conditionally drop, then recreate and populate the test table.
-- None of this is part of the solution. It just provides a
-- test bed for the coded solution that follows this section.
IF OBJECT_ID('TempDB..#PivotExample') IS NOT NULL
DROP TABLE #PivotExample
CREATE TABLE #PivotExample (PartNo VARCHAR(12),RoleDesc VARCHAR(64))
INSERT INTO #PivotExample
SELECT '102031' , 'Exec. Service Employee, Jeff A.' UNION ALL
SELECT '102031' , 'Service Employee Group, Global Call Center' UNION ALL
SELECT '102031' , 'Sold-To Party, McLaren Center' UNION ALL
SELECT '102503' , 'Exec. Service Employee, Jeff A.' UNION ALL
SELECT '102503' , 'Service Employee Group, Global Call Center' UNION ALL
SELECT '102503' , 'Sold-To Party, Center' UNION ALL
SELECT '5-903-61587', 'Exec. Service Employee, Jeff A.' UNION ALL
SELECT '5-903-61587', 'Service Employee Group, Global Call Center' UNION ALL
SELECT '5-903-61587', 'Sold-To Party, Center'
--===== Solution starts here
--===== Conditionally drop the work table
IF OBJECT_ID('Tempdb..#NormalNVP') IS NOT NULL
DROP TABLE #NormalNVP
--===== Declare the dynamic SQL Variables
DECLARE @SQLSelect VARCHAR(8000),
@SQLSelectList VARCHAR(8000),
@SQLFrom VARCHAR(8000)
--===== Split the RoleDesc and save the data in a temp table because
-- we're going to use it more than once. We could use the a
-- CTE twice, but the code for it would be executed twice and
-- it would simply make life a bit more difficult. Temp table
-- is a lot easier here because it allows for very simple
-- "Divide'n'Conquer" programming... might be faster, too.
SELECT *
INTO #NormalNVP
FROM (--==== Split the RoleDesc column like it should have been split in the
-- original NVP (Name/Value Pair) table.
SELECT PartNo,
SUBSTRING(RoleDesc,1,CHARINDEX(',',RoleDesc)-1) AS NVPRole,
SUBSTRING(RoleDesc,CHARINDEX(',',RoleDesc)+2,8000) NVPValue
FROM #PivotExample
) d
--===== Create the static part of the SELECT
SELECT @SQLSelect = ' SELECT PartNo,' + CHAR(10)
--===== Create the dynamic SELECT list
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
+ 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
+ ' THEN NVPValue END) AS ' + QUOTENAME(NVPRole)
FROM #NormalNVP
GROUP BY NVPRole
--===== Create the static FROM clause
SELECT @SQLFrom = '
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
PRINT @SQLSelect + @SQLSelectList + @SQLFrom
--===== Execute the Dynamic SQL to solve the problem
EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)
[/font]
Heh... oh yeah... almost forgot... the next question that usually comes up for such a problem is "how can I control the order of the columns?" The answer to that is if the column contains name contains some sorting clue such as numbers or is alphabetically correct, the dynamic SQL will take care of that "auto-magically". If the column name contains no such sort clue, then you must have prior knowledge of which columns will be returned so that you can control the order of appearance from left to right and that brings you right back to the original problem of having to know which columns will be returned. Like this...
[font="Courier New"] SELECT PartNo,
MAX(CASE WHEN NVPRole = 'Service Employee Group'
THEN NVPValue END) AS [Service Employee Group],
MAX(CASE WHEN NVPRole = 'Exec. Service Employee'
THEN NVPValue END) AS [Exec. Service Employee],
MAX(CASE WHEN NVPRole = 'Sold-To Party'
THEN NVPValue END) AS [Sold-To Party]
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2009 at 2:32 pm
Thanks for bringing some clarity here Jeff, I think I may have confused things by turning data in earlier post into a temporary table!
Many thanks for your post; I had been having some difficulty following your Cross Tabs and Pivots article, understanding this trivial example has given me enough confidence to tackle it again, cheers!
Allister
May 23, 2009 at 8:33 pm
Thanks for the feedback, Allister.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2009 at 7:31 am
Ok Jeff, again thanks a lot for your time and patience, turns out your article http://www.sqlservercentral.com/articles/cross+tab/65048/ and subsequent discussions answered all the queries I had — must spend a little more time reading before posting questions already asked and answered elsewhere! Unfortunately my vocabulary is still limited in this area, so finding the information can sometimes be difficult/time-consuming.
Cheers
May 24, 2009 at 4:59 pm
Allister Reid (5/24/2009)
Ok Jeff, again thanks a lot for your time and patience, turns out your article http://www.sqlservercentral.com/articles/cross+tab/65048/ and subsequent discussions answered all the queries I had — must spend a little more time reading before posting questions already asked and answered elsewhere! Unfortunately my vocabulary is still limited in this area, so finding the information can sometimes be difficult/time-consuming.Cheers
That's one of the things about IT that still bugs me. If you don't know what to ask for, how can you do a search to find it? It's like the old dictionary paradox... to find the correct spelling of a word, you have to at least come close. And, to know which word to lookup, you must already know it's meaning.
Again, thanks for the feedback, Allister. Let us know if you run into another hard spot.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2009 at 11:46 am
Wow, thanks guys! 🙂 This has been really helpful and thanks for taking the time to explain this!
I'm going to work on this now and see if I can make my whole script work, he he he... I might be back.
Michelle
May 27, 2009 at 12:41 pm
Aw heck, I'm in over my head! :crying: I don't know what to do.
Jeff, your script work perfect, and I was basically able to understand it. So I tried to incorporate it into my script and it's not working out so good. I tried creating a CTE with this one, but I'm kinda lost.
I think I need some help again! I could kick myself in the butt because you basically gave me the answer and I still can't figure it out.
Michelle :unsure:
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply