SQL Server Performance

Query help

Discussion in 'SQL Server 2005 General Developer Questions' started by Raj1979, Sep 28, 2006.

  1. Raj1979 New Member

    Can some one write a query for the below requirement. here is the code that you can use to create and populate the tables

    Create table XYZ (id int, item1 uniqueidentifier)
    create table ABC( item2 uniqueidentifier,description varchar(10))

    INSERT INTO XYZ VALUES(1, 'ABC77892-DEF8-FE72-9F6E-000CCD9A958F')
    INSERT INTO XYZ VALUES(2, 'ABC77892-DEF8-FE72-9F6E-000000000000')
    INSERT INTO XYZ VALUES(3, 'ABC77892-D000-FE72-9F6E-000000000000')

    iNSERT INTO ABC VALUES ('ABC77892-DEF8-FE72-9F6E-000CCD9A958F', 'DEF')
    iNSERT INTO ABC VALUES ('ABC77892-DEF8-FE72-9F6E-000CCD9A958F', 'GHI')
    iNSERT INTO ABC VALUES ('ABC77892-DEF8-FE72-9F6E-000CCD9A958F', 'JKL')
    iNSERT INTO ABC VALUES ('ABC77892-DEF8-FE72-9F6E-000000000000', 'XXX')
    iNSERT INTO ABC VALUES ('ABC77892-DEF8-FE72-9F6E-000000000000', 'YYY')
    iNSERT INTO ABC VALUES ('ABC77892-DEF8-FE72-9F6E-000000000000', 'zzz')


    -- Now I want a select statement which joins the above tables on Item1 and Item2 and the resul should be as shown below. There are three columns in the result ID, Item1, Concatnated description


    1 ABC77892-DEF8-FE72-9F6E-000CCD9A958F DEF, GHI,JKL
    2 ABC77892-DEF8-FE72-9F6E-000000000000 XXX,YYY,ZZZ
    3. ABC77892-D000-FE72-9F6E-000000000000 Null

    Thanks in advance for the help
  2. Madhivanan Moderator

    http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true


    Madhivanan

    Failing to plan is Planning to fail
  3. bogdan New Member

    Nice post Madhivanan [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Here you go Raj1979 ... [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />select <br />x.id,<br />x.item1, <br />dbo.ConcatXYZABC(x.item1) as description<br />from xyz x<br /><br /><br />CREATE FUNCTION [dbo].[ConcatXYZABC](@item1 uniqueidentifier)<br />RETURNS VARCHAR(8000)<br />AS<br />BEGIN<br />DECLARE @Output VARCHAR(8000)<br />SET @Output = ''<br /><br />SELECT @Output =CASE @Output <br />WHEN '' THEN description <br />ELSE @Output + ', ' + description <br />END<br />FROM abc<br />WHERE item2 = @item1<br />ORDER BY item2<br /><br />RETURN @Output<br />END<br /><br />Able was I ere I saw Elba !

Share This Page