Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL syntax question

Posted on 2010-01-04
13
Medium Priority
?
201 Views
Last Modified: 2012-05-08
I have a table that contains 1 item (ORDNO) to many Category relationship.  I need assistance with achieving the following...

Current ORDERS table contents look like this.

ORDNO|NAME            |FREQUENCY      |Category            
-------------------------------------------------------------------
1      |Ativan in D5%      |Q2H                |Decrease anxiety
1      |Ativan in D5%      |Q2H            |IV Solution
2      | Clindamycin      |Q8H            |Antibiotic
2      | Clindamycin      |Q8H            |IV Solution

I would like help with T-SQL syntax to only have one row per ORDNO while combining the Category columns from ORDERS like as follows.

ORDNO|NAME            |FREQUENCY      |Category            
-------------------------------------------------------------------
1      |Ativan in D5%      |Q2H                |Used to Decrease anxiety;IV Solution
2      |Clindamycin      |Q8H            |Antibiotic; IV Solution

Any help is GREATLY appreciated!
0
Comment
Question by:JRM874
  • 8
  • 3
  • 2
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26173286
create function dbo.RetCategory
( @OrderNo int  )
returns varchar(8000)
as
begin
  declare @ret varchar(8000)
  SELECT @Ret = COALESCE (@Ret+';', '') + category
  from Orders
  where OrdNo = @orderNo
  return (@ret )
end

go

select ORDNO, name ,frequency, category = dbo.RetCategory (OrdNo )
from Orders
group by ORDNO, name ,frequency
0
 

Author Comment

by:JRM874
ID: 26173297
Wow that was fast!  Please allow me to test and I will award points if no further questions  thanks again!
0
 

Author Comment

by:JRM874
ID: 26173339
Failed to mention that the orders table is a #temp table being compiled in a stored procedure.  will this function call work with a temp table?  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JRM874
ID: 26173374
Msg 2772, Level 16, State 1,
Cannot access temporary tables from within a function.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26173422
which version of sql sever ?
0
 

Author Comment

by:JRM874
ID: 26173427
SQL Server 2005
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26173474

SELECT    
    ORDNO, name ,frequency, category = REPLACE(
        (
                  SELECT category AS [data()]
                  FROM #Orders soh
                  WHERE soh.ORDNO = c.ORDNO
                  ORDER BY  ORDNO
                  FOR XML PATH ('')
                  ), ' ', ',')
FROM #Orders c
ORDER BY ORDNO
0
 

Author Comment

by:JRM874
ID: 26173656
This appears to have done the trick.  Can you please tell me what you are doing with the following...
--------------------
FOR XML PATH ('')
 ), ' ', ',')
---------------------
Each word in the category is now seperated by a comma. ',' assuming this is because the last argument
is this absolutly nesc?
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26174412
If you do not mind leaving the trailing "; " then this will work

SELECT    
    ORDNO, name ,frequency, category = (
                  SELECT category + '; ' AS [data()]
                  FROM #Orders soh
                  WHERE soh.ORDNO = c.ORDNO
                  ORDER BY  ORDNO
                  FOR XML PATH ('')
                  )
FROM #Orders c
ORDER BY ORDNO
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26174515
FYI,  the "FOR XML PATH ('')" is using the built in SQL XML routines to concatinate all the value returned in the inner select into one line of XML data, because we pass an empty PATH ('') it will not surround the data with tags <tag>data</tag> as it would if there were a value inside the ('tag') like that.
In his original example, the "), ' ', ',')" was the end of the REPLACE command which was replacing every blank space, ' ', with a comma hince your comma between every word.  The REPLACE trick is neat and I have recommeded it often, but it does not work if you have embeded spaces in your data as you do.
0
 

Author Comment

by:JRM874
ID: 26174554
Sorry for the dumb questions, new to this...  it appears that 'FOR XML PATH' is only used so that ORDER BY can be used in the subquery.  Replace () is what was putting the , in place of spaces.   CGLuttrell: thanks for the input but this does not consolidate the rows.  I was able to use SELECT DISTINCT to do that and removing the REPLACE() function got rid of the comma's.  
0
 

Author Closing Comment

by:JRM874
ID: 31672520
Very quick response!
0
 

Author Comment

by:JRM874
ID: 26174586
CGLuttrell: thank you very much for the detailed explaination!  I certainly appriciate it!  
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question