How to combine multiple row one but differentiate with the first character

Transact-SQL
1

sql

did anyone know how to merge the data from multiple row into 1 base on the nameID?
Also, they SubKey need to merge accordingly to the first character?

Thanks in advance for any help.

2

Hi dexk,

Please try my solution:

/*sample DB table definition*/

CREATE TABLE dbo.persons

(
  ID int IDENTITY(1,1) PRIMARY KEY,
  name varchar(100),
  SubKey varchar(10),
  nameID numeric(9)
);

CREATE INDEX idxqq1 ON dbo.persons(SubKey, nameID);

-----------------------------------------

/*sample DB inserts*/

INSERT INTO dbo.persons
VALUES('John', 'A001', 10091001);

INSERT INTO dbo.persons
VALUES('david', 'A002', 10091002);

INSERT INTO dbo.persons
VALUES('annie', 'A003', 10091003);

INSERT INTO dbo.persons
VALUES('kelly', 'A004', 10091004);

INSERT INTO dbo.persons
VALUES('david', 'D1190', 10091002);

INSERT INTO dbo.persons
VALUES('anne', 'D1199', 10091005);

INSERT INTO dbo.persons
VALUES('elsy', 'B110', 10091006);

INSERT INTO dbo.persons
VALUES('John', 'E1000', 10091001);

INSERT INTO dbo.persons
VALUES('kelly', 'B100', 10091004);

INSERT INTO dbo.persons
VALUES('John', 'C1010', 10091001);



select * from dbo.persons;

-----------------------------------------

/*final select*/
SELECT
--
	ROW_NUMBER() OVER(ORDER BY nameID) AS NewID, name, nameID,
--
	MIN(CASE WHEN SubKey LIKE 'A%' THEN SubKey ELSE NULL END) AS SubKeyA,
	MIN(CASE WHEN SubKey LIKE 'B%' THEN SubKey ELSE NULL END) AS SubKeyB,
	MIN(CASE WHEN SubKey LIKE 'C%' THEN SubKey ELSE NULL END) AS SubKeyC,
	MIN(CASE WHEN SubKey LIKE 'D%' THEN SubKey ELSE NULL END) AS SubKeyD,
	MIN(CASE WHEN SubKey LIKE 'E%' THEN SubKey ELSE NULL END) AS SubKeyE
--
FROM dbo.persons
GROUP BY name, nameID
ORDER BY 1;

Limitation of this solution:
Maximum 1 record could exist within a nameID and SubKey group!

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler

3

hi

please google search
"" how to make column into rows

PIVOT and UNPIVOT is one way
there are other ways

4

Hi

Several Ways of combining multiple rows into one

5

Hi

I have another solution

i think THIS FIXES .......

Limitation of this solution:
Maximum 1 record could exist within a nameID and SubKey group!
as per gigawatt38

Anybody please check and let me know

My SQL Script Solution 
SELECT a.NAME, 
       a.nameid, 
       asub, 
       bsub, 
       csub, 
       dsub, 
       esub 
FROM   (SELECT DISTINCT NAME, 
                        nameid 
        FROM   persons) a 
       LEFT JOIN (SELECT nameid, 
                         LEFT(subkey, 1) AS Asubkey, 
                         subkey          AS Asub 
                  FROM   persons 
                  WHERE  LEFT(subkey, 1) = 'A') b 
              ON a.nameid = b.nameid 
       LEFT JOIN (SELECT nameid, 
                         LEFT(subkey, 1) AS Bsubkey, 
                         subkey          AS Bsub 
                  FROM   persons 
                  WHERE  LEFT(subkey, 1) = 'B') c 
              ON a.nameid = c.nameid 
       LEFT JOIN (SELECT nameid, 
                         LEFT(subkey, 1) AS Bsubkey, 
                         subkey          AS Csub 
                  FROM   persons 
                  WHERE  LEFT(subkey, 1) = 'C') d 
              ON a.nameid = d.nameid 
       LEFT JOIN (SELECT nameid, 
                         LEFT(subkey, 1) AS Bsubkey, 
                         subkey          AS Dsub 
                  FROM   persons 
                  WHERE  LEFT(subkey, 1) = 'D') e 
              ON a.nameid = e.nameid 
       LEFT JOIN (SELECT nameid, 
                         LEFT(subkey, 1) AS Bsubkey, 
                         subkey          AS Esub 
                  FROM   persons 
                  WHERE  LEFT(subkey, 1) = 'E') f 
              ON a.nameid = f.nameid 
ORDER  BY nameid

Regards,