Click here to Skip to main content
14,667,169 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 12-May-17 5:20am
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
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
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
   
v3
Comments
sekar305 12-May-17 12:35pm
   
but NO,and code(column) is datatype is varchar , qty only numeric
CHill60 12-May-17 12:47pm
   
I've updated the loop to cater for [No] being a varchar. It is very bad practice to use varchar to store numbers by the way.
Maciej Los 16-May-17 10:47am
   
5 for the effort!

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100