Merge Multiple Rows in SQL Server

-
I was just going through a problem in database which was related to querying data from two tables but all the uncommon columns should be merged to a single row even comma separated.

Primary Table
ID
Value_1
Value_2
1
ABC
DEF
2
GEH
IJK


Foreign Table
ID
FK_ID
Value_1
1
1
LMK
2
1
IJKK
3
2
ZZZ
4
2
YYY

 Expected Result
PK.ID
FK_ID
PK.Value_1
FK_Value_1
PK.Value_2
1
1122,2122
ABC
LMK,IJKK
LKM
2
3122,4122
GEH
ZZZ,YYY
NOP

Query
Select PK.ID,PK.Value_1, PK.Value_2,
      (SELECT STUFF( (SELECT ', ' +  CONVERT(varchar(25), FK.ID, 1) 
            FROM FOREIGN_TABLE FK  WHERE FK.ID=PK.ID
                  FOR XML PATH ('')) , 1, 2, ''))   AS FK_ID ,
      (SELECT STUFF( (SELECT ', ' +  CONVERT(varchar(25), FK2.Value_1, 1)
             FROM FOREIGN_TABLE FK2 WHERE FK2.ID=PK.ID 
                  FOR XML PATH ('')) , 1, 2, ''))   AS FK_Value_1 
FROM PRIMARY_TABLE PK 

This query will be performance wise slow but, in SQL Server we don't have group concat like MySQL so, we will have to bear the pain.

Comments

Post a Comment

Popular posts from this blog

DDOS (Distributed Denial of Service) Attack in Java

-
We often hear about the word DOS attack on a site, and few people often think like DOS only means Disk Operating System. But, here DOS is something big, and we use it to crash and operational site by consuming the quote of daily or monthly or yearly requests of a site. Here I would like to share a basic program using RMI based bots that will be helpful for reading purpose only. My intention is not to develop hacker but just to give an idea of using RMI in JAVA. First we have created an interface that will be concreted in it's implementation later. But, the main purpose behind creating this interface is just for RMI as the look up functionality of RMI needs an interface instead of complete implementation. import java.rmi.*; // DDOSService Interface // Interface for a RMI service that will actually attack on a target machine public interface DDOSService extends java.rmi.Remote { public String attack ( ) throws RemoteException; } Now this time is just to pro...
Read more

First Formula 1 Car

-
I have seen so many students in my life as I also do some voluntarily advisory service for IT students.  Not, only this but have also tried to promote the work of those students, where I think the work in not extraordinary but, they have the skill-set to do something beyond the thoughts. Well, Pakistan is considered to be the country lives under the threats of terrorist and the people here always talk about the stuff which is not healthy for anyone including themselves. But, if this tail can be considered true then why different countries on the globe, hire the people from Pakistan and India. As everyone is aware that the people here are talented but unfortunately due to project funding issues and the growing country problems people have started migrating to different lands where they can find peace. As the first statement was not a rumor and it's a bitter reality but along with that the people are talented and always try to do something innovated which can't be expect...
Read more

Maven + Struts2 + Spring + Hibernate + Struts2-Convention-Plugin

-
Image
After spending so many days and nights and days again I finally be able to achieve the basic Architecture. Well, the basic architecture was quite simple and you will be able to get a lot of tutorials online about it but, probably they have few things missing (i.e. Maven or sometimes Struts2-Convention-Plugin). Reason behind choosing this structure is to make an easy expandable application structure with an isolation in between layers. Reason Behind: Maven is just used for dependency management of libraries and easy deployment. Struts2  is used for UI. Spring for service layer, as we all know we can easily change implementation which will be helpful for us to entertain the change request. Hibernate for ORM that will play with the database. Directory Structure: Don't worry about such a big directory tree. One by one I will tell you each and everything about it. Before starting everything we will start telling you about the pom that will be required to setup the ...
Read more