Merge Multiple Rows into a logic

• sqlquery29

  Ten Centuries

  Points: 1319

  More actions

  January 31, 2019 at 5:53 am

  #379279

  Good Day,

  Please find below my requirement :

  --SOURCE TABLE
  CREATE TABLE [dbo].[SourceTable](
      [P_NUMBER] [varchar](20) NULL,
      [DATE] [varchar](20) NULL,
      [READING] [varchar](40) NULL,
      [CONSUMPTION] [varchar](40) NULL
      ) ;

  GO
  -- OUT PUT TABLE, OUTPUT SHOULD LOOK LIKE IN THE TABLE

  CREATE TABLE [dbo].[OUTPUTTable](
      [P_NUMBER] [varchar](20) NULL,
      [DATE] [varchar](20) NULL,
      [READING] [varchar](200) NULL,
      [CONSUMPTION] [varchar](200) NULL
  ) ;

  GO

  INSERT INTO SourceTable
  VALUES ('211097895','01.11.2017','1359878','X'),
   ('211097895','01.12.2017','1378167','18289.00'),
   ('211097895','01.01.2018','1395996','17829.00'),
   ('211097895','01.02.2018','1416809','20813.00'),
   ('211097895P','01.11.2017','1289029','X'),
   ('211097895P','01.12.2017','1311579','22550.00'),
   ('211097895P','01.01.2018','1324551','12972.00'),
   ('211097895P','01.02.2018','1340885','16334.00'),
   ('211097895D','01.11.2017','363000','X'),
   ('211097895D','01.12.2017','383000','20000.00'),
   ('211097895D','01.01.2018','388000','5000.00'),
   ('211097895D','01.02.2018','396000','8000.00'),
   ('211097895K','01.11.2017','2396767','X'),
   ('211097895K','01.12.2017','2427865','31098.00'),
   ('211097895K','01.01.2018','2455009','27144.00'),
   ('211097895K','01.02.2018','2487880','32871.00'),
   ('211097895S','01.11.2017','3159478','X'),
   ('211097895S','01.12.2017','3212158','52680.00'),
   ('211097895S','01.01.2018','3243733','31575.00'),
   ('211097895S','01.02.2018','3284570','40837.00');
  GO

      
  GO

  INSERT INTO OUTPUTTable
  VALUES ('211097895','01.11.2017','1359878;1289029;363000;2396767;3159478','X;X;X;X;X'),
      ('211097895','01.12.2017','1378167;1311579;383000;2427865;3212158','18289.00;22550.00;20000.00;31098.00;52680.00'),
      ('211097895','01.01.2018','1395996;1324551;388000;2455009;3243733','17829.00;12972.00;5000.00;27144.00;31575.00'),
      ('211097895','01.02.2018','1416809;1340885;396000;2487880;3284570','20813.00;16334.00;8000.00;32871.00;40837.00');
      

      
  In the Source data the P_NUMBER is a product number, It has versions with an extension of alphabet 'P', 'D', 'S', 'K' and it has got readings against each on a date, I need to merge the readings and consumptions( CONSUMP) of all 'P', 'D', 'S', 'K' into single field separated by semi-colon (';')

  I did provide the sample data above, Please let me know for a solution.

  I did try queries using Stuff function with no luck, Appreciate your help.

  Regards.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  January 31, 2019 at 8:58 am

  #2020718

  This design is absolutely HORRIBLE.  Push back and say "No" before it's too late.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 31, 2019 at 9:19 am

  #2020722

  sqlquery29 - Thursday, January 31, 2019 5:53 AM

  
  I did try queries using Stuff function with no luck, Appreciate your help.
  

  Can you show what you tried?
  I hope that you're not storing the concatenated values and this is for display purposes only.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• fahey.jonathan

  Hall of Fame

  Points: 3617

  More actions

  January 31, 2019 at 11:20 am

  #2020746

  SELECT    P_Number,
    Date,
    MAX(CASE WHEN Extension = '' THEN Reading ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'P' THEN Reading ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'D' THEN Reading ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'K' THEN Reading ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'S' THEN Reading ELSE '' END) AS Reading,
    MAX(CASE WHEN Extension = '' THEN Consumption ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'P' THEN Consumption ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'D' THEN Consumption ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'K' THEN Consumption ELSE '' END) + ';' +
    MAX(CASE WHEN Extension = 'S' THEN Consumption ELSE '' END) AS Consumption
FROM    (
    SELECT    CASE WHEN P_Number LIKE '%[PDKS]' THEN LEFT(P_Number, LEN(P_Number) - 1) ELSE P_Number END AS P_Number,
        CASE WHEN P_Number LIKE '%[PDKS]' THEN RIGHT(P_Number, 1) ELSE '' END AS Extension,
        Date,
        Reading,
        Consumption
    FROM    SourceTable
    ) q
GROUP BY P_Number,
    Date
ORDER BY P_Number,
    Date


• fahey.jonathan

  Hall of Fame

  Points: 3617

  More actions

  January 31, 2019 at 11:26 am

  #2020748

  By the way, the above code assumes no nulls in the Reading and Consumption fields.  If there can be nulls, then the code should be adjusted to account for them in the base SELECT:
     SELECT    CASE WHEN P_Number LIKE '%[PDKS]' THEN LEFT(P_Number, LEN(P_Number) - 1) ELSE P_Number END AS P_Number,
        CASE WHEN P_Number LIKE '%[PDKS]' THEN RIGHT(P_Number, 1) ELSE '' END AS Extension,
        Date,
        ISNULL(Reading, '') AS Reading,
        ISNULL(Consumption, '') AS Consumption
    FROM    SourceTable


• sqlquery29

  Ten Centuries

  Points: 1319

  More actions

  February 1, 2019 at 1:31 am

  #2020818

  Thanks Fahey...It works perfectly for my requirement.

• okfine08

  SSC Rookie

  Points: 49

  More actions

  February 3, 2019 at 7:23 am

  #2020960

  Luis Cazares - Thursday, January 31, 2019 9:19 AM

  sqlquery29 - Thursday, January 31, 2019 5:53 AM

  
  I did try queries using Stuff function with no luck, Appreciate your help.
  

  Can you show what you tried?
  I hope that you're not storing the concatenated values and this is for display purposes only.

  HI 

  I TRIED WITH THE STUFF FUNCTION

  HOPE IT HELPS
  🙂
  🙂

  DROP TABLE [SourceTable]
GO

  CREATE TABLE [dbo].[SourceTable](
[P_NUMBER] [varchar](20) NULL,
[DATE] [varchar](20) NULL,
[READING] [varchar](40) NULL,
[CONSUMPTION] [varchar](40) NULL
) ;

  GO
-- OUT PUT TABLE, OUTPUT SHOULD LOOK LIKE IN THE TABLE

  DROP TABLE [OUTPUTTable]
GO
CREATE TABLE [dbo].[OUTPUTTable](
[P_NUMBER] [varchar](20) NULL,
[DATE] [varchar](20) NULL,
[READING] [varchar](200) NULL,
[CONSUMPTION] [varchar](200) NULL
) ;

  GO

  INSERT INTO SourceTable
VALUES ('211097895','01.11.2017','1359878','X'),
('211097895','01.12.2017','1378167','18289.00'),
('211097895','01.01.2018','1395996','17829.00'),
('211097895','01.02.2018','1416809','20813.00'),
('211097895P','01.11.2017','1289029','X'),
('211097895P','01.12.2017','1311579','22550.00'),
('211097895P','01.01.2018','1324551','12972.00'),
('211097895P','01.02.2018','1340885','16334.00'),
('211097895D','01.11.2017','363000','X'),
('211097895D','01.12.2017','383000','20000.00'),
('211097895D','01.01.2018','388000','5000.00'),
('211097895D','01.02.2018','396000','8000.00'),
('211097895K','01.11.2017','2396767','X'),
('211097895K','01.12.2017','2427865','31098.00'),
('211097895K','01.01.2018','2455009','27144.00'),
('211097895K','01.02.2018','2487880','32871.00'),
('211097895S','01.11.2017','3159478','X'),
('211097895S','01.12.2017','3212158','52680.00'),
('211097895S','01.01.2018','3243733','31575.00'),
('211097895S','01.02.2018','3284570','40837.00');
GO

  GO

  INSERT INTO OUTPUTTable
VALUES ('211097895','01.11.2017','1359878;1289029;363000;2396767;3159478','X;X;X;X;X'),
('211097895','01.12.2017','1378167;1311579;383000;2427865;3212158','18289.00;22550.00;20000.00;31098.00;52680.00'),
('211097895','01.01.2018','1395996;1324551;388000;2455009;3243733','17829.00;12972.00;5000.00;27144.00;31575.00'),
('211097895','01.02.2018','1416809;1340885;396000;2487880;3284570','20813.00;16334order by .00;8000.00;32871.00;40837.00');
GO

  SELECT * FROM OUTPUTTable
SELECT * FROM SourceTable

  ; WITH CTE AS
(
SELECT
REPLACE(REPLACE(REPLACE(REPLACE([P_NUMBER],'D',''),'K',''),'S',''),'P','') AS [P_NUMBER] ,
[DATE] ,
[READING] ,
[CONSUMPTION]
FROM
SourceTable
)
SELECT SS.[P_NUMBER],
   SS.[DATE],
   STUFF(
   (
    SELECT '; ' + [READING]
    FROM CTE US
    WHERE SS.P_NUMBER = US.P_NUMBER
         AND US.[DATE] = SS.[DATE]     
    FOR XML PATH('')
   ),
   1,
   1,
   ''
    ) [COLUMN TO ROW],
            STUFF(
   (
    SELECT '; ' + [CONSUMPTION]
    FROM CTE US
    WHERE SS.P_NUMBER = US.P_NUMBER
         AND US.[DATE] = SS.[DATE]     
    FOR XML PATH('')
   ),
   1,
   1,
   ''
    ) [COLUMN TO ROW1]
FROM CTE SS
GROUP BY SS.[P_NUMBER],
   SS.[DATE]
ORDER BY 1;

  

• sqlquery29

  Ten Centuries

  Points: 1319

  More actions

  February 4, 2019 at 3:47 am

  #2021009

  Thanks okfine08, This is also working but the query is running forever on the main database which has around 200K records