-1

I have a query that looks like this:

select Item_Number,Size_Description from Style_Size
where Company_Code='01' and Division_Code='008'
and Item_Number='18SP6726B024B' and Color_Code='00605'

This is the table that it returns

+-----------------+------------------+
|   Item_Number   | Size_Description |
+-----------------+------------------+
| 18SP6726B024B   | SMALL            |
| 18SP6726B024B   | MEDIUM           |
| 18SP6726B024B   | LARGE            |
| 18SP6726B024B   | X-LARGE          |
+-----------------+------------------+

How do I make it so it shows all Size Descriptions on one field so it would look like

18SP6726B024B   SMALL /MEDIUM /LARGE/X-LARGE

Is this possible or would it have to be four different fields through a pivot? And if so, how would I make sure it will work for all item numbers?

nathan
  • 83
  • 7
  • What version of sql server are you using? Do you have a fixed max number of sizes or is this needing to dynamic? – Sean Lange Nov 15 '19 at 19:29
  • SQL Server 2008 or whichever one does not have STRING_AGG. It would have to be dynamic. – nathan Nov 15 '19 at 19:45
  • Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – Eric Nov 15 '19 at 19:45
  • Use STUFF and FOR XML – Sean Lange Nov 15 '19 at 19:47
  • @Eric Those are for having them in different columns. I need it all in one column. – nathan Nov 15 '19 at 19:48
  • 1
    Also tons of example. Here's one of them. https://stackoverflow.com/questions/15477743/listagg-in-sqlserver – Eric Nov 15 '19 at 20:04

2 Answers2

1

You really need to consider upgrading from 2008. It has been out of support for several months now. You can use the STUFF FOR XML technique for this. It is just a lot more code and effort than the much simpler STRING_AGG we have in the current versions.

This should work for you given the sample data you provided. But you probably want a different order for the sizes. There was nothing in your sample data to indicate a proper sort order there so I just used the Size_Description. You will either need a better sorting column or deal with the sizes in alphabetical order.

select Item_Number
    , STUFF((Select '/' + Size_Description 
            from Style_Size ss
            where ss.Company_Code = s.Company_Code
                and ss.Division_Code = s.Division_Code
                and ss.Item_Number = s.Item_Number
                and ss.Color_Code = s.Color_Code
                order by ss.Size_Description --You might want/need a different column to order by instead of the size description
                FOR XML PATH('')), 1,1 ,'')
from Style_Size s
where Company_Code = '01' 
    and Division_Code = '008'
    and Item_Number = '18SP6726B024B'
    and Color_Code = '00605'
group by s.Item_Number
    , s.Company_Code
    , s.Division_Code
    , s.Color_Code
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Thanks a lot! I wish I could upgrade but this is for a company I work for, the CTO would have to upgrade all of us. Where can I read more about STUFF and for XML? I don't fully understand it. – nathan Nov 15 '19 at 20:07
  • That is two different things working together. First is STUFF which is used to...well...STUFF strings into other strings. You can google this technique and there are thousands and thousands of examples all over the place. – Sean Lange Nov 15 '19 at 20:23
  • Hey Sean, I was wondering if you could help my update the query. I am trying to add another field so I did "Select '/' + Size_Description + '/' + Cast(Quantity_Of_Size_Per_Carton as nvarchar)" for the first line but I get an error String would be truncated. Do I have to make a change to the FOR XML line? – nathan Nov 19 '19 at 19:44
  • If you don't specify the scale of your nvarchar it will very likely not be long enough. Use nvarchar(max) and you should be good. – Sean Lange Nov 19 '19 at 20:54
0

I think you are looking for STRING_AGG and a GROUP BY

So something like

select Item_Number, STRING_AGG(Size_Description, ' /') 
from Style_Size
where Company_Code='01' and Division_Code='008'
and Item_Number='18SP6726B024B' and Color_Code='00605'
GROUP BY Item_Number
EdmCoff
  • 3,506
  • 1
  • 9
  • 9