How to select resultant rows in a single column in SQL server

Question

0.00/5 (No votes)

See more:

i have two tables  table 1 get single value and table 2 more than value in same id

I have a table like this:

+-------+------+------+
|No     |code  | qty  |
+-------+------+------+
|1055   |956   |  10  |
+-------+------+------+
|1055   |957   |  9   |
+-------+------+------+
|1055   |958   |  5   |
+-------+------+------+
|1055   |959   |  4   |
+-------+------+------+
|1055   |960   |  3   |
+-------+------+-----+
 
i need rows value in single column without using sub query
The output should be something like:

+------+----------------+----------+
|No    |code   |           qty     |
+------+----------------+----------+
|1055  |956,957,958,959 | 10,9,5,4 |
+------+------+---------+----------+

how to get value using sql server

What I have tried:

 SELECT   Stuff( (SELECT N','+ table1.code
 from table1   inner join table2 on  table1.no=table2.no
where table1.trackno in ('17-05-SC-0009') order by table1.trackno desc
     For XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'),1,1,N'') as [MobileNos]

Posted 12-May-17 4:10am

sekar305

Updated 12-May-17 5:20am

Add a Solution

Comments

CHill60 12-May-17 10:39am

You say you have two tables but you have only shown us one of them, and haven't said which one it is!
Also explain what is wrong with the code that you already have.
Also please explain why you cannot have a sub-query

sekar305 12-May-17 10:57am

1.in table 1 having No(1st column) and second table 2 having No,code, qty
when i was inner join means it showing duplication look like the above table,

(ex:

+-------+------+------+
|No |code | qty |
+-------+------+------+
|1055 |956 | 10 |
+-------+------+------+
|1055 |957 | 9 |
+-------+------+------+
|1055 |958 | 5 |
+-------+------+------+
|1055 |959 | 4 |
+-------+------+------+
|1055 |963 | 3 |
+-------+------+------+
|1056 |958 | 5 |
+-------+------+------+
|1056 |959 | 4 |

2. in my code all rows value showing in single column
+------+----------------+--------------+
|No |code | qty |
+------+----------------+---------------+
|1055 |956,957,958,959 | 10,9,5,4,5,4 |
+------+------+---------+--------------+

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-05-12T05:20:00

The only thing you have missed in your query is the GROUP BY clause, but using FOR XML PATH you are going to have to use correlated sub-queries I think. i.e.

SQL

SELECT
    t1.[no]
        ,STUFF((SELECT ', ' + cast(t2.code as varchar)
               FROM test t2
               WHERE t1.[no]=t2.[no] ORDER BY t2.code
               FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),1,1, N'') AS codes

        ,STUFF((SELECT ', ' + cast(t2.qty as varchar)
               FROM test t2
               WHERE t1.[no]=t2.[no] ORDER BY t2.code
               FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),1,1, N'') AS qtys
FROM test t1
GROUP BY t1.[no]

Which gives the results

No       Codes                           Qtys
1055	 956, 957, 958, 959, 960	 10, 9, 5, 4, 3
1056	 958, 959	                 5, 4

[NOTE - I just created a table test that had your data from the question in it - replace "test" with your table join]

If you absolutely cannot use sub-queries then you can use COALESCE but the only way I could get that to work was with a loop (yuk!) like this

SQL

DECLARE @start int = (SELECT CAST(MIN([No]) AS Int) FROM test)
DECLARE @end int = (SELECT CAST(MAX([No]) AS Int) FROM test)
DECLARE @results table ([no] varchar(4), codes varchar(max), qtys varchar(max))
WHILE @start <= @end
BEGIN
	DECLARE @listCode VARCHAR(MAX) = null
	DECLARE @listQty VARCHAR(MAX) = null
	SELECT  @listCode = COALESCE(@listCode+',' ,'') + CAST(code AS Varchar),
		@listQty = COALESCE(@listQty+',' ,'') + CAST(qty AS Varchar)
	FROM test
	WHERE [no] = CAST(@start AS Varchar)
	
	IF @listCode IS NOT NULL
	BEGIN
		INSERT INTO @results  ([no], codes, qtys) VALUES (cast(@start as varchar), @listCode, @listQty)
	END

	SET @start += 1
END
SELECT * FROM @Results