put results in a string

Hi,

Below query gives me below results.

select ea.list,a.ObjectID

      from case1 a, case2 ea

      where a.ObjectID = '101'

      and a.caseID = ea.AssessmentID

      and a.caseID = (SELECT max(a.caseID)

                          from case1 a, case2 ea

                        where a.ObjectID = '101'

Results:

list    ObjectID

aa      101
bb      101
cc       101
dd       101

bb       102
jj           102


i want modify above sql to give me results as below

101     aa,bb,cc,dd
102     bb,jj

Thanks.
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can try the following method in your query

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400) ,(2,100),(2,200),(3,300),(3,400)



SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Open in new window

0
PortletPaulfreelancerCommented:
Line 12 of your existing query (see below)

That is BAD.

SELECT
      ea.list
    , a.ObjectID
FROM case1 a
   , case2 ea --<< ye olde syntax, so last century
WHERE a.ObjectID = '101'
  AND a.caseID = ea.AssessmentID --<< use a join instead
  AND a.caseID = (
        SELECT
              MAX(a.caseID)
        FROM case1 a
           , case2 ea --<< a Cartesian Product! for NOGOOD REASON !!

        WHERE a.ObjectID = '101'
        )

Open in new window

Here is a slightly improved version that removes the accidental Cartesian product
SELECT
      ea.list
    , a.ObjectID
FROM case1 a
INNER JOIN case2 ea ON a.caseID = ea.AssessmentID --<< used a join instead
WHERE a.ObjectID = '101'
  AND a.caseID = (
        SELECT
              MAX(a.caseID)
        FROM case1 a
        WHERE a.ObjectID = '101'
        )

Open in new window

Those accidental Cartesian products can really slow down queries - a lot. They occur because you are using old fashioned join syntax within the where clause. If you use full ANSI join syntax such accidents are avoided.
0
sam2929Author Commented:
Hi Paul,
I didn't see the solution.

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
no, i didn't offer one. I saw something else you needed to be aware of.

Vikas did offer a solution
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I have an article out there called T-SQL:  Normalized data to comma delineated string and back with images and sample code which is essentially the same as Vikas' solution.
0
sam2929Author Commented:
Vikas can't we do this in sql ?
0
PortletPaulfreelancerCommented:
? the solution Vikas has offered is in T-SQL not sure what your last question is for.

The approach used in SQL Server requires "FOR XML PATH" it is quite a common technique and requires no external extensions.

You can use a scalar function approach instead, there are probably quite a few to choose from but I generally propose "FOR XML PATH". Having said that I prefer to use an APPLY operator instead of doing it in the select clause.
SELECT DISTINCT
         ID
       , ca.List_Output
FROM @Table1 t
CROSS APPLY (
             SELECT
                STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
             FROM @Table1 
             WHERE ID = t.ID
             FOR XML PATH(''), TYPE)
            .value('.','NVARCHAR(MAX)'),1,2,' ') 
          ) ca (List_Output)

Open in new window

0
sam2929Author Commented:
how can i add above code in below sql.


select ea.list,a.ObjectID

      from case1 a, case2 ea

      where a.ObjectID = '101'

      and a.caseID = ea.AssessmentID

      and a.caseID = (SELECT max(a.caseID)

                          from case1 a, case2 ea

                        where a.ObjectID = '101'
0
PortletPaulfreelancerCommented:
You would not add to that incomplete and incorrect query. You would replace it.

From this sample data
    CREATE TABLE case1  
        ([ObjectID] int, [caseID] int)
    ;
        
    INSERT INTO case1  
        ([ObjectID], [caseID])
    VALUES
        (101, 101),
        (102, 102)
    ;
    
    CREATE TABLE case2   
        ([list] varchar(2), [AssessmentID] int)
    ;
        
    INSERT INTO case2   
        ([list], [AssessmentID])
    VALUES
        ('aa', 101),
        ('bb', 101),
        ('cc', 101),
        ('dd', 101),
        ('bb', 102),
        ('jj', 102)
    ;
    

Open in new window

Using this query
    SELECT DISTINCT
             a.caseID
           , ca.List_Output
    FROM Case1 a
    CROSS APPLY (
                 SELECT
                    STUFF((SELECT ', ' + ea.List
                 FROM Case2 ea
                 WHERE ea.AssessmentID = a.caseID
                 FOR XML PATH(''), TYPE)
                .value('.','NVARCHAR(MAX)'),1,2,' ') 
              ) ca (List_Output)
;

Open in new window

I get this result:
    | caseID |     List_Output |
    |--------|-----------------|
    |    101 |  aa, bb, cc, dd |
    |    102 |          bb, jj |

  [1]: http://sqlfiddle.com/#!3/b4edf/2

Open in new window


Whilst your initial question was not unreasonable, if you provide us with "sample data" and "expected result" solutions generally take way less time. Note that the sample data is "per table" so that we can test the query. nb: I "reverse engineered" the sample data I used, it may be quite inadequate.

If you need it, you can include the where clause you had before to the new query with the correction made to remove the accidental Cartesian product.
    SELECT DISTINCT
             a.caseID
           , ca.List_Output
    FROM Case1 a
    CROSS APPLY (
                 SELECT
                    STUFF((SELECT ', ' + ea.List
                 FROM Case2 ea
                 WHERE ea.AssessmentID = a.caseID
                 FOR XML PATH(''), TYPE)
                .value('.','NVARCHAR(MAX)'),1,2,' ') 
              ) ca (List_Output)
    WHERE a.ObjectID = 101
      AND a.caseID = (
              SELECT
                    MAX(a.caseID)
              FROM case1 a
              WHERE a.ObjectID = '101'
              );

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
sam2929, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.