Replicating VBA solution in SQL

Question

I have a SQL query that provides me with the output in the 'Output Before' pic.
Column 5 if essentially doing a countif on column1 for items in column1, similar to Excel.
I would like to add some code/sub query, so that the output becomes like the 'Output After' pic.

Does anyone have any ideas how I can do this?
I can do it in excel with VBA but just cant get my head around how to do it in SQL.

Output Before

Output After

Always helps when asking a database question to state what type of database you're working with. — Tim Williams, Jan 18 '19 at 17:05
That's the software you're using to access the database. Not the database/version. — Nathan Champion, Jan 18 '19 at 17:08
What should the output look like if you had different values in `column2` for `Dave`? — Tony, Jan 18 '19 at 17:22
Whatever the values in column2 are when the original output is created, they will stay the same in the final output — Aquelious, Jan 18 '19 at 17:26

score 0 · Answer 1 · answered Jan 18 '19 at 17:27

0

Since you are using SQL Server 2017 you can get what you want with STRING_AGG:

select column1, column2, string_agg(column3, '&') as column3, column4
from outputbefore
group by column1, column2, column4

answered Jan 18 '19 at 17:27

forpas

160,666
10
38
76

score 0 · Answer 2 · answered Jan 18 '19 at 17:35

You're looking to concatenate multiple rows into a single value. Your options depend on your version of SQL Server. In older versions (I think 2005+) you have to use a torturous XML procedure. Run this on your server and you'll see how it works, but I'll leave it to you to work out the query for your data:

SELECT  STUFF( 
     (SELECT    ', <' + name + '>'
      FROM      sys.databases
      WHERE     database_id > 4
      ORDER BY  name
        FOR   XML PATH('') ,
                  ROOT('MyString') ,
                  TYPE 
     ).value('/MyString[1]', 'varchar(max)'), 1, 2, '') AS namelist;

As of SQL 2017 you can use the STRING_AGG function, explained here.

score 0 · Answer 3 · answered Jan 18 '19 at 17:44

As I see this is a string concatenation issue. I used a replace to handle the character & in XML.

select a.col1, Col3=replace(stuff((SELECT '#' + b.col3 AS 'data()'
FROM OutputBefore) b 
where b.Col1=a.Col1
FOR XML PATH('')),1,1,''),'#','&')
from (select distinct Col1 from OutputBefore) a;

Hayder Nahee · Answer 4 · 2019-01-21T14:29:33.883

As mentioned by forpas and Russell, as of SQL 2017 you're able to use STRING_AGG function.

For SQL 2008+ Refer back to this:

How Stuff and 'For Xml Path' work in Sql Server

In your case you want the delimiter to be '&' which will cause a problem with FOR XML PATH due to XML Special character. So you'll want to escape XML Special characters, example:

DECLARE @TableA TABLE (Col1 NVARCHAR(10), Col2 INT, Col3 NVARCHAR(10), Col4 

NVARCHAR(10), Col5 INT)

INSERT INTO @TableA (Col1, Col2, Col3, Col4, Col5)
VALUES  ('Dave'     , 24    , 'house'   , 'married'     , 2)
    ,   ('Dave'     , 24    , 'car'     , 'married'     , 2)
    ,   ('Bob'      , 32    , 'House'   , 'single'      , 1)
    ,   ('George'   , 12    , 'house'   , 'divorced'    , 1)

SELECT
        t2.Col1
    ,   t2.Col2
    ,   STUFF   (   (   SELECT '&' + Col3 -- Adding '&' as delimited
                        FROM @TableA t1
                        WHERE t1.Col2 = t2.Col2
                        FOR XML PATH (''), TYPE
                    ).value('.', 'VARCHAR(MAX)'),1,1,''-- To escape special characters
                ) AS Col3
    ,   t2.Col4
FROM @TableA AS t2

GROUP BY    t2.Col1
        ,   t2.Col2
        ,   t2.Col4

Replicating VBA solution in SQL

4 Answers4