Get single row & 2 columns result: with count(*) in 1st col and concatenated strings in 2nd col

Question

Table: I have a database table table_1 in SQL Server 2012 with data as:

CREATE TABLE table_1 ([ID] BIT, [Name] VARCHAR(10))

INSERT INTO table_1 ([ID],[Name]) VALUES (1,'A')
INSERT INTO table_1 ([ID],[Name]) VALUES (0,'B')
INSERT INTO table_1 ([ID],[Name]) VALUES (0,'C')

What I want is a single row with two columns in the result. Like:

col_1   col_2
2       B, C have zero value

So I want to know how many entries in column [ID] are 0 and then display count of those as col_1 in result. Then col_2 should have all the (concatenated) strings from column [name] when column [ID] had 0. THis later part to get concatenated strings from column [name] is what I am unable to get.

It must be very simple but I can't get it done beyond this SQL:

 SELECT COUNT(*) AS col_1 FROM table_1 WHERE ID = 0;

How can I add second column col_2 that'll have concatenated strings from col_2? If the count(*) is zero then col_2 can be empty. (Would be great if I can add NA)

I tried to use solutions from these threads url1 and url2 but couldn't customize it to get count(*) as zero when ID column doesn't have any 0.

Please describe your desired output more clearly. Provide sample data and exactly what result you expect to see. — Daniel Marcus, May 22 '18 at 20:44
I have already provided minimal sample data and the desired output as well. I am not sure what more details are needed. — 300, May 22 '18 at 20:51
Your sample data has three records and your expected outcome shows one record. It's unclear how the final result will relate to the original data. For example, is the expected outcome partitioned by id? If so, your expected outcome shows no ID values so theres no way to know what ID has col_1=2 and col_2= B, C have zero value — Daniel Marcus, May 22 '18 at 21:05
`Your sample data has three records and your expected outcome shows one record.` That is because column ID is of type BIT which will have only 0 or 1. Also as I mentioned in the question: What I want is a single row with two columns in the result. That is the expected result I want. For the rest of the clarification you asked: ID is being used to filter out records in where condition. I want count not actual ID values which I tried to mention using `SELECT COUNT(*) AS col_1 FROM table_1 WHERE ID = 0;` If you run the CEATE & INSERT SQLs I provided, you'll get the exact table. — 300, May 22 '18 at 21:27
Please let me know if I can provide any more details. I can understand it's difficult to explain and understand each other through just messages at times. I can also improve my question with more details. Thank you for your responses and time. — 300, May 22 '18 at 21:29

score 1 · Accepted Answer · answered May 24 '18 at 20:22

Take the solution for the string concatenation you already linked in a subquery to get the text. Put it together with another subquery for the count. And select the one as col_1 the other as col_2.

SELECT (SELECT count(*)
               FROM table_1
               WHERE ID = 0) col_1,
        (SELECT substring((SELECT ', ' + Name [text()]
                                  FROM table_1
                                  WHERE ID = 0
                                  FOR XML PATH('')),
                          3,
                          len((SELECT ', ' + Name [text()]
                                      FROM table_1
                                      WHERE ID = 0
                                      FOR XML PATH(''))) - 2)) col_2;

(Tested on SQL Server 2014, but as far as I understand the docs, there shouldn't be anything in there, version 2012 can't do.)

Get single row & 2 columns result: with count(*) in 1st col and concatenated strings in 2nd col

1 Answers1