asked on

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.

Vikas Garg

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

PortletPaul

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.

sam2929

ASKER

Hi Paul,
I didn't see the solution.

Thanks

PortletPaul

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

Vikas did offer a solution

Jim Horn

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.

sam2929

ASKER

Vikas can't we do this in sql ?

PortletPaul

? 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

sam2929

ASKER

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'

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

sam2929, do you still need help with this question?