Combine rows into a single table

• jdswarrior

  Valued Member

  Points: 55

  More actions

  August 22, 2018 at 1:21 pm

  #372133

  I want to combine rows into a single row
  below is the data

  create table test
  (
  col1 varchar(5) null,
  col2 varchar(5) null
  )
  go
  insert into test values ('A', 'B')
  insert into test values ('C','D')
  insert into test values ('E','F')

  select

  * from test
  yields:

  col1col2

  A   B

  C   D

  E   F

  I want one

  row

  with values A B C D E F

  how can this be done?

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  August 22, 2018 at 1:35 pm

  #2002929

  jdswarrior - Wednesday, August 22, 2018 1:21 PM

  I want to combine rows into a single row
  below is the data

  create table test
  (
  col1 varchar(5) null,
  col2 varchar(5) null
  )
  go
  insert into test values ('A', 'B')
  insert into test values ('C','D')
  insert into test values ('E','F')

  select * from test
  yields:

  col1col2

  A   B

  C   D

  E   F

  I want one row with values A B C D E F

  how can this be done?

  Try this:
  DROP TABLE IF EXISTS #test;

  CREATE TABLE #test
(
  col1 VARCHAR(5) NULL
, col2 VARCHAR(5) NULL
);
GO

  INSERT #test
VALUES
(
  'A', 'B'
)
,(
  'C', 'D'
)
,(
  'E', 'F'
);

  DECLARE @X VARCHAR(MAX) = '';

  SELECT @X = CONCAT(@X, t.col1, ' ', t.col2, ' ')
FROM #test t;

  SELECT @X;

  ---

• Thom A

  SSC Guru

  Points: 99143

  More actions

  August 22, 2018 at 1:36 pm

  #2002931

  Have you tried anything so far? If not, consider the simple + operator and FOR XML PATH. Have a go, and if you get stuck please do reply with your attempt so that we can help you further.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jdswarrior

  Valued Member

  Points: 55

  More actions

  August 22, 2018 at 2:16 pm

  #2002940

  Phil Parkin - Wednesday, August 22, 2018 1:35 PM

  jdswarrior - Wednesday, August 22, 2018 1:21 PM

  I want to combine rows into a single row
  below is the data

  create table test
  (
  col1 varchar(5) null,
  col2 varchar(5) null
  )
  go
  insert into test values ('A', 'B')
  insert into test values ('C','D')
  insert into test values ('E','F')

  select * from test
  yields:

  col1col2

  A   B

  C   D

  E   F

  I want one row with values A B C D E F

  how can this be done?

  Try this:
  DROP TABLE IF EXISTS #test;

  CREATE TABLE #test
(
  col1 VARCHAR(5) NULL
, col2 VARCHAR(5) NULL
);
GO

  INSERT #test
VALUES
(
  'A', 'B'
)
,(
  'C', 'D'
)
,(
  'E', 'F'
);

  DECLARE @X VARCHAR(MAX) = '';

  SELECT @X = CONCAT(@X, t.col1, ' ', t.col2, ' ')
FROM #test t;

  SELECT @X;

  yes that works - I should have been more specific - can I get output values in 6 separate columns?

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  August 22, 2018 at 2:55 pm

  #2002946

  jdswarrior - Wednesday, August 22, 2018 2:16 PM

  Phil Parkin - Wednesday, August 22, 2018 1:35 PM

  jdswarrior - Wednesday, August 22, 2018 1:21 PM

  I want to combine rows into a single row
  below is the data

  create table test
  (
  col1 varchar(5) null,
  col2 varchar(5) null
  )
  go
  insert into test values ('A', 'B')
  insert into test values ('C','D')
  insert into test values ('E','F')

  select * from test
  yields:

  col1col2

  A   B

  C   D

  E   F

  I want one row with values A B C D E F

  how can this be done?

  Try this:
  DROP TABLE IF EXISTS #test;

  CREATE TABLE #test
(
  col1 VARCHAR(5) NULL
, col2 VARCHAR(5) NULL
);
GO

  INSERT #test
VALUES
(
  'A', 'B'
)
,(
  'C', 'D'
)
,(
  'E', 'F'
);

  DECLARE @X VARCHAR(MAX) = '';

  SELECT @X = CONCAT(@X, t.col1, ' ', t.col2, ' ')
FROM #test t;

  SELECT @X;

  yes that works - I should have been more specific - can I get output values in 6 separate columns?

  I have to ask WHY?  It doesn't scale if you don't use dynamic SQL, and it won't scale well if you do.  If this is for a report, do this in your reporting software.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  August 22, 2018 at 3:54 pm

  #2002953

  jdswarrior - Wednesday, August 22, 2018 2:16 PM

  Phil Parkin - Wednesday, August 22, 2018 1:35 PM

  Try this:
  DROP TABLE IF EXISTS #test;

  CREATE TABLE #test
  (
    col1 VARCHAR(5) NULL
  , col2 VARCHAR(5) NULL
  );
  GO

  INSERT #test
  VALUES
  (
    'A', 'B'
  )
  ,(
    'C', 'D'
  )
  ,(
    'E', 'F'
  );

  DECLARE @X VARCHAR(MAX) = '';

  SELECT @X = CONCAT(@X, t.col1, ' ', t.col2, ' ')
  FROM #test t;

  SELECT @X;

  yes that works - I should have been more specific - can I get output values in 6 separate columns?

  I'm not saying this is a good solution, but this adjustment to Phil's solution will make them into columns...

  DROP TABLE IF EXISTS #test;

  CREATE TABLE #test
(
col1 VARCHAR(5) NULL
, col2 VARCHAR(5) NULL
);
GO

  INSERT #test
VALUES
('A', 'B'),('C', 'D'),('E', 'F');

  DECLARE @X VARCHAR(MAX) = '';

  SELECT @X =
  'select ' + STUFF(
  (SELECT DISTINCT ', ''' + t.[col1] + ''' , ''' + t.[col2] + '''' , ' '
   FROM #test t FOR XML PATH(''), TYPE
  ).value('.', 'nvarchar(max)'), 1, 1, '');

  EXEC (@x);


• jdswarrior

  Valued Member

  Points: 55

  More actions

  August 24, 2018 at 12:22 pm

  #2003265

  work well - thx - i have an alternative solution already developed - wanted to compare the two and see what works best in powershell - we are looping thru a list of over 200 servers in a text file and want to run a SQLCMD and get a list of linked servers and data_source names from sys.servers