How to concatenate same columns different values in group by clause in SQL 2008R2

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi ,
How to Concatenate same columns different values in group by clause in sql 2008R2?

I want to select count of shopkeeper Registration and their amount limit against a dealer kindly guide me.

What I have tried:

SQL

select COUNT( ShopkeeperID)TotalReg,ShopkeeperID,DealerID  from InsuranceMaster
where ShopkeeperID=4
group by ShopkeeperID,DealerID

its showing result

C#

TotalReg	ShopkeeperID	DealerID	AmountLimit
1	             4	           21	            1
1	             4	           21	            6

My desired result is

C#

TotalReg	ShopkeeperID	DealerID      AmountLimit
   2                4	          21            1,6

Posted 29-Nov-16 19:25pm

Noman Suleman

Updated 29-Nov-16 22:59pm

Tomas Takac

v4

Add a Solution

Comments

Mehdi Gholam 30-Nov-16 1:35am

Why?

Noman Suleman 30-Nov-16 1:38am

its user's requirement

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)

$*Developer - Vaibhav*$ · Accepted Answer · 2016-11-29T22:59:00

Solution 1

C#

Create TABLE #x( ShopkeeperID INT, DealerID INT, amtlimit int);

INSERT #X(ShopkeeperID, DealerID, amtlimit) VALUES
(1,1,20), (1,1,30), (2,1,20),  (2,2,20),
(2,2,20), (3,1,20), (3,1,20),  (3,2,60),
(3,2,60);

############################################


 SELECT COUNT(t.ShopkeeperID) as tcount, t.ShopkeeperID, t.DealerID
       ,STUFF((SELECT ', ' + CAST(t1.amtlimit AS VARCHAR(10)) [text()]
         FROM #x t1
         WHERE t1.DealerID = t.DealerID  and t1.ShopkeeperID = 3 
FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM #x t
where t.ShopkeeperID = 3
GROUP BY t.ShopkeeperID, t.DealerID

Posted 29-Nov-16 22:59pm

$*Developer - Vaibhav*$

Comments

$*Developer - Vaibhav*$ 30-Nov-16 5:00am

out put like this

tcount ShopkeeperID DealerID List_Output
2 3 1 20, 20
2 3 2 60, 60

Noman Suleman 30-Nov-16 5:27am

Thanks its perfect :)

Noman Suleman 30-Nov-16 8:19am

hi, when i trying to get column from other table with inner join its not getting desired result kindly guide and check my query

SELECT COUNT( t.ShopkeeperID) as tcount,t.DealerID, t.ShopkeeperID
,STUFF((SELECT ', ' + CAST(Config_LimitInsurance.Name AS VARCHAR(10)) [text()]
FROM InsuranceMaster t1
WHERE t1.ShopkeeperID = t.ShopkeeperID and t1.DealerID = 21
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM InsuranceMaster t INNER JOIN

Config_LimitInsurance ON t.AmountLimit = Config_LimitInsurance.ID
WHERE (t.DealerID = 21)
GROUP BY t.DealerID, t.ShopkeeperID, Config_LimitInsurance.Name

result is

tcount DealerID ShopkeeperID List_Output
2 21 4 50k, 50k
1 21 10 50k
1 21 11854 50k

$*Developer - Vaibhav*$ 1-Dec-16 5:41am

please share both table structure and value?

Noman Suleman 1-Dec-16 7:09am

thanks. i have done it by call query in views now its work fine showing my desire result with the help of your query thanks again