IF loops within Select Statements SQL Server 2008?

Question

Let's say I have a table called Details with 4 columns:OrderNo, Name, Qty, and Weight. I am iterating through the table and selecting the needed fields, concatenating a string out of the data. For example:

-----------------------------------------------------------------
|     OrderNo     |    Name           |     Qty   |     Weight  |
-----------------------------------------------------------------
|     a1234       |    HDMI_Cable     |     2     |     0.1     |
|     a1234       |    Aux_Cable      |     1     |     0.1     |
|     b5346       |    2.1_Speakers   |     1     |     3.5     |
|     t5485       |    3.5mm_Adapter  |     5     |     0.1     |
-----------------------------------------------------------------

So with this data, what I would do is iterate through the table and concatenate the data separated by a semicolon (;), then each row separated by a '>'. For the second and third row, this would give me:

a1234;Aux_Cable;1;0.1>b5346;2.1_Speakers;1;3.5>

The complicated part is that when the order number is the same, I'd like to append it to the previous row without repeating the order number. For example, row 1 and 2 would look like:

a1234;HDMI_Cable;2;0.1;Aux_Cable;1;0.1>

I have tried using IF loops and cases with nested select statements, but I keep running into endless errors and can never seem to get it to work. Anyone have any idea on how to do this or if it can be done? Thanks for reading!

No I am not using one. – The_DemoCorgin Dec 13 '13 at 17:40 — The_DemoCorgin, Dec 13 '13 at 17:40

score 2 · Accepted Answer · edited May 23 '17 at 12:11

You can use the SQL Server XML extensions to concatenate rows, but because of your requirements, you will need to do this twice:

The first one will be to concatenate similar orders:

SELECT  OrderConcat = OrderNo 
                        + ( SELECT  ';' + Name 
                                        + ';' + CAST(Qty AS VARCHAR(10)) 
                                        + ';' + CAST(Weight AS VARCHAR(10))
                            FROM    T T2
                            WHERE   T.OrderNo = T2.OrderNo
                            FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)')
FROM    T
GROUP BY OrderNo;

This gives:

ORDERCONCAT
------------------------------------
a1234;HDMI_Cable;2;0.10;Aux_Cable;1;0.10
b5346;2.1_Speakers;1;3.50
t5485;3.5mm_Adapter;5;0.10

Example on SQL Fiddle

The next step is to then concatenate these into a single row:

WITH Orders AS
(   SELECT  OrderConcat = OrderNo 
                            + ( SELECT  ';' + Name 
                                            + ';' + CAST(Qty AS VARCHAR(10)) 
                                            + ';' + CAST(Weight AS VARCHAR(10))
                                FROM    T T2
                                WHERE   T.OrderNo = T2.OrderNo
                                FOR XML PATH(''), TYPE
                                ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM    T
    GROUP BY OrderNo
)
SELECT  STUFF(( SELECT  '>' + OrderConcat
                FROM    Orders
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX'), 1, 1, '');

Giving a final result of:

a1234;HDMI_Cable;2;0.10;Aux_Cable;1;0.10>b5346;2.1_Speakers;1;3.50>t5485;3.5mm_Adapter;5;0.10

Example on SQL Fiddle

If I wanted to add in a count of how many rows an order number has after it would this be possible? Like: `a1234;HDMI_Cable;2;0.10;Aux_Cable;1;0.10;Count2>b5346;2.1_Speakers;1;3.50;Count1>t5485;3.5mm_Adapter;5;0.10;Count1` — The_DemoCorgin, Dec 13 '13 at 19:29
Yes . I'm only on my mobile at the moment so can't test but after the first `.value('.','NVARCHAR(MAX)')` You'd need something like `; count' + CAST(COUNT(*) AS VARCHAR(10))`. I'll add a tested version to the answer tomorrow when I can. — GarethD, Dec 13 '13 at 21:01
If I declare some variables in the script, is there a way to increment them for each time the Select statement runs? Or can you not SET int values within a select statement? Sorry for all the questions — The_DemoCorgin, Dec 13 '13 at 22:46
[Working example with count](http://sqlfiddle.com/#!3/c46da/21). I don't understand what you mean by increment a variable each time the select statement runs. Sounds like you could want the ROW_NUMBER function. I'd suggest asking a new question if ROW_NUMBER does not help. — GarethD, Dec 15 '13 at 09:57

score 1 · Answer 2 · answered Dec 13 '13 at 17:59

This is probably easiest to do with SQL Server's handling of XML if you've got non-string fields you'd need to convert them, otherwise:

SELECT DISTINCT OrderNo + ';'+
        STUFF((SELECT DISTINCT ';' +  Name + ';'+Qty+';'+Weight                 
               FROM Table1 b
               WHERE a.OrderNo = b.OrderNo
                                    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                                    ,1,1,'')+'>'
FROM Table1 a

Demo: SQL Fiddle

Just like @Gareth suggested. Looks like just what I need thanks! — The_DemoCorgin, Dec 13 '13 at 18:02

IF loops within Select Statements SQL Server 2008?

2 Answers2