approach used by Taryn for SQL concatenation

Question

I have this table in SQL:

pid           displayname   Group
-------------------------------------
d-5454-s34    name          Frans
d-5454-s34    sd            xyh
d-5454-s34    description   Group zen
d-5454-s34    member        xxxx
d-5454-s34    member        yyyy
d-5454-s34    member        zzzzz
d-5454-s34    member        uuuuu
d-5454-s45    name          He-man
d-5454-s45    sd            ygh
d-5454-s45    description   Group Comics
d-5454-s45    member        eeee
d-5454-s45    member        ffffff
e-3434-t45    name          Calvin
e-3434-t45    sd            trdg
e-3434-t45    description   test

and the final output should be

pid                name          sd      descript          member
---------------------------------------------------------------------------

d-5454-s34        Frans         xyh      Group zen       xxxx; yyyy; zzzzz; uuuuu

d-5454-s45        He-man        ygh      Group Comics    eeee; ffffff

e-3434-t45        Calvin        trdg     test                 NULL

I have used the approach of Taryn that you can see in this link: Sql PIVOT and string concatenation aggregate

... and for now I only want to build the concatenated string so:

SELECT distinct a.pid, a.displayname, LEFT(r.[Description] , LEN(r.[Description])-1) [Description]
  FROM [Teste] a
    CROSS APPLY
(
    SELECT r.[description] + '; ' 
    FROM [Teste] r
    where a.[pid] = r.[pid]
      and a.[displayname] = r.[displayname]
    FOR XML PATH('')
) r ([Description])

but to have the member concatenated is taking lots of time and it throws this error: Could not allocate space for object 'dbo.Large Object Storage System object: 422858089496576' in database 'tempdb' because the 'PRIMARY' filegroup is full.

IF i use a small set (using select top 100 in select r.[description]) it is giving a table like the following (it is only a part of the table):

      pid              displayname          descript          
    ---------------------------------------------------------------------------

    d-5454-s34        member              xxxx; xxxx; xxxx; xxxx; (and it keeps to repeat)

   d-5454-s34        member               yyyy; yyyy; yyyy; yyyy;  (and it keeps to repeat)

    d-5454-s45        member             eeee; eeee; eeee; eeee; (and it keeps to repeat)

    d-5454-s45        sd                  ygh; ygh;ygh;ygh;ygh;ygh;  (and it keeps to repeat)

it does not show distinct pIds neither, and it repeats the values.. it does not show different values for the member for each id, neither single values for sd, description, and name.

Your table design has a lot of issues. You have an EAV going on here which is pretty painful. Do you always have name, sd, description and member rows for every single id? Do you have other values in there as well? Once of the challenges of EAV is putting the information back together in a meaningful way. — Sean Lange, Jun 25 '18 at 15:57
each id has always filled the name, description and sd. Each Id can have ZERO (rare), ONE or more members. — graphene, Jun 25 '18 at 16:12
yes: name, sd and description contains one and only one value. By one value I mean for one particular PID... the name, sd and description only have one row, never more rows for each PID. But for a particular PID we can have 0 rows (not common), 1 row or more than 1 row for the MEMBER. for the sake of clarity: the PID d-5454-s45 contains only one value for description, name and sd, but two values for member (in the example they are eeee and fffff). note: it is pid , not id, i forgot the "p" letter, sorry for my mistake. — graphene, Jun 26 '18 at 09:14

Gordon Linoff · Accepted Answer · 2018-06-26T11:43:37.057

2

select distinct might solve your problem. But you have other problems with your logic.

I think this does what you want:

SELECT a.*, 
       (SELECT STUFF( (SELECT DISTINCT '; ' + r.[description]
                       FROM [tdt_AD_Teste] r
                       WHERE a.pid = r.pid AND
                             r.[displayname] = 'member'
                       FOR XML PATH('')
                      ), 1, 2, ''
                    )
       ) Description
FROM (SELECT a.pid,
             MAX(CASE WHEN a.displayname = 'name' then [group] end) as name,
             MAX(CASE WHEN a.displayname = 'sd' then [group] end) as sd,
             MAX(CASE WHEN a.displayname = 'description' then [group] end) as description
      FROM tdt_AD_Teste a
      GROUP BY a.pid
     ) a;

edited Jun 26 '18 at 11:43

answered Jun 25 '18 at 15:45

Gordon Linoff

1,242,037
58
646
786

Inside the select stuff ( WHERE a.[id] = r.[id] AND (...) it says a.[id] is an invalid column name. there is no alias "a" inside the select distinct assigned... so.. another wrap?... – graphene Jun 25 '18 at 19:04
1

@graphene . . . The `a` is referring to the alias of the subquery in the outer `FROM`. – Gordon Linoff Jun 26 '18 at 01:31
it is not working, the id in a.id is not really the id of the table... it is just a code (and you can see it repeats in the first table). with your code it throws this error: Invalid column name 'id'. even if I use it in max(case when a.id=.......). it still delivers null for all table. I replaced id by pid to not be confused with the id of table. And using this pid it says Invalid column name 'pid' as it was not declared inside the second FROM. Check the tables above (in the original post). – graphene Jun 26 '18 at 09:18
1

@graphene . . . *Your* question originally had `id`. You appear to have changed it to `pid`. – Gordon Linoff Jun 26 '18 at 11:44
got it. it works like a gem. Is there a book you can recommend on good practices to write SQL scripts? Thanks! Kudos. – graphene Jun 26 '18 at 12:14
2

@graphene if this answer works for you then you should mark this as the answer. That way the responder and anybody else who wanders in here over the years know this answer worked for this situation. – Sean Lange Jun 26 '18 at 13:34

approach used by Taryn for SQL concatenation

1 Answers1

Linked