Pivot Tables

• CRC

  Old Hand

  Points: 399

  More actions

  August 10, 2012 at 8:06 am

  #392930

  I have a table called 'warehouse_locations'. A row in the table looks like this:

  site_idlocation_iddescription

  0618a4 18a4

  06 18a5 18a5

  I need the results to be as follows:

  location1 location2

  18a4 18a5

  I have looked at the pivot explanations and have not been successful so far. Please help

  ---

  Kindest Regards,

  CRC

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  August 10, 2012 at 8:10 am

  #1524452

  Ok, saw you edited. justa sec.

• CRC

  Old Hand

  Points: 399

  More actions

  August 10, 2012 at 8:11 am

  #1524453

  Sorry I accidentaly posted before I was finished writing.

  ---

  Kindest Regards,

  CRC

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  August 10, 2012 at 8:14 am

  #1524456

  CRC (8/10/2012)

  ---

  I have a table called 'warehouse_locations'. A row in the table looks like this:

  site_idlocation_iddescription

  0618a4 18a4

  06 18a5 18a5

  I need the results to be as follows:

  location1 location2

  18a4 18a5

  I have looked at the pivot explanations and have not been successful so far. Please help

  Ok, PIVOT turns row VALUES into COLUMN headings.

  so, how do i know that location1 is 18a4 and location2 is 18a5?

  is the number of columns you expect a fixed or dynamic number?

• CRC

  Old Hand

  Points: 399

  More actions

  August 10, 2012 at 8:20 am

  #1524460

  The number of columns returning are dynamic. I will be selecting a range and will need them returned as one row.

  ---

  Kindest Regards,

  CRC

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 10, 2012 at 9:53 am

  #1524536

  I can be wrong but I think dynamic SQL with the loop is the only way:

  CREATE TABLE #Site (

  site_id CHAR(2)

  ,location_id CHAR(4)

  ,[description] VARCHAR(100)

  );

  INSERT INTO #Site (site_id, location_id, [description])

  VALUES ('06', '18a4', '18a4'),

  ('06', '18a5', '18a5'),

  ('06', '18a6', '18a6'),

  ('06', '18a7', '18a7'),

  ('06', '18a8', '18a8'),

  ('06', '18a8', '18a8'),

  ('06', '18a9', '18a9')

  --SELECT * FROM #Site;

  DECLARE @sql VARCHAR(1000);

  DECLARE @i INT; SET @i = 0;

  DECLARE @location_id CHAR(4);

  CREATE TABLE #Temp (location_count INT);

  INSERT INTO #Temp(location_count) VALUES(0);

  WHILE EXISTS(SELECT * FROM #Site) BEGIN

  SET @i = @i + 1;

  SELECT TOP (1) @location_id = location_id FROM #Site;

  SET @sql = 'ALTER TABLE #Temp ADD location' + CAST(@i AS VARCHAR(9)) + ' CHAR(4)';

  EXEC (@sql);

  SET @sql = 'UPDATE #Temp SET location' + CAST(@i AS VARCHAR(9)) + ' = ''' + @location_id + '''';

  EXEC (@sql);

  DELETE FROM #Site WHERE location_id = @location_id;

  END;

  UPDATE #Temp SET location_count = @i;

  --Or you can delete location_count

  --ALTER TABLE #Temp DROP COLUMN location_count;

  SELECT * FROM #Temp;

  DROP TABLE #Temp;

  DROP TABLE #Site;

  --Vadim R.

• CRC

  Old Hand

  Points: 399

  More actions

  August 10, 2012 at 10:10 am

  #1524543

  Thanks!!! That works great!

  ---

  Kindest Regards,

  CRC

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 10, 2012 at 10:14 am

  #1524544

  Glad it worked for you.

  --Thanks.

  --Vadim R.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 10, 2012 at 10:34 am

  #1524549

  whats supposed to happen if you have these records?

  INSERT INTO #Site (site_id, location_id, [description])

  VALUES ('06', '18a4', '18a4'),

  ('06', '18a5', '18a5'),

  ('06', '18a6', '18a6'),

  ('06', '18a7', '18a7'),

  ('06', '18a8', '18a8'),

  ('06', '18a9', '18a9'),

  ('07', '19a5', '19a5')

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• CRC

  Old Hand

  Points: 399

  More actions

  August 10, 2012 at 1:40 pm

  #1524644

  In this particular instance there won't be different site id's because the data that will be inserted into the temp table will only be for one site.

  ---

  Kindest Regards,

  CRC

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  August 10, 2012 at 8:27 pm

  #1524734

  rVadim (8/10/2012)

  ---

  I can be wrong but I think dynamic SQL with the loop is the only way:

  CREATE TABLE #Site (

  site_id CHAR(2)

  ,location_id CHAR(4)

  ,[description] VARCHAR(100)

  );

  INSERT INTO #Site (site_id, location_id, [description])

  VALUES ('06', '18a4', '18a4'),

  ('06', '18a5', '18a5'),

  ('06', '18a6', '18a6'),

  ('06', '18a7', '18a7'),

  ('06', '18a8', '18a8'),

  ('06', '18a8', '18a8'),

  ('06', '18a9', '18a9')

  --SELECT * FROM #Site;

  DECLARE @sql VARCHAR(1000);

  DECLARE @i INT; SET @i = 0;

  DECLARE @location_id CHAR(4);

  CREATE TABLE #Temp (location_count INT);

  INSERT INTO #Temp(location_count) VALUES(0);

  WHILE EXISTS(SELECT * FROM #Site) BEGIN

  SET @i = @i + 1;

  SELECT TOP (1) @location_id = location_id FROM #Site;

  SET @sql = 'ALTER TABLE #Temp ADD location' + CAST(@i AS VARCHAR(9)) + ' CHAR(4)';

  EXEC (@sql);

  SET @sql = 'UPDATE #Temp SET location' + CAST(@i AS VARCHAR(9)) + ' = ''' + @location_id + '''';

  EXEC (@sql);

  DELETE FROM #Site WHERE location_id = @location_id;

  END;

  UPDATE #Temp SET location_count = @i;

  --Or you can delete location_count

  --ALTER TABLE #Temp DROP COLUMN location_count;

  SELECT * FROM #Temp;

  DROP TABLE #Temp;

  DROP TABLE #Site;

  The loop and the resulting multiple updates can be avoided. Please see the following article.

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 10, 2012 at 11:14 pm

  #1524739

  Jeff Moden (8/10/2012)

  ---

  The loop and the resulting multiple updates can be avoided. Please see the following article.

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  Thank you, Jeff, great article.

  OK, there is another way, without loop and ALTER/UPDATE.

  CREATE TABLE #Site (

  site_id CHAR(2)

  ,location_id CHAR(4)

  ,[description] VARCHAR(100)

  );

  INSERT INTO #Site (site_id, location_id, [description])

  VALUES ('06', '18a4', '18a4'),

  ('06', '18a5', '18a5'),

  ('06', '18a6', '18a6'),

  ('06', '18a7', '18a7'),

  ('06', '18a9', '18a8'),

  ('06', '18a8', '18a8'),

  ('06', '18a9', '18a9')

  --SELECT * FROM #Site

  DECLARE @sql VARCHAR(2000) = 'SELECT ';

  DECLARE @i INT = 1;

  WITH DistinctLocationsCTE AS (

  SELECT DISTINCT location_id

  FROM #Site

  )

  SELECT

  @sql += '''' + COALESCE(location_id, '') + ''' AS location' + CAST(@i AS VARCHAR(9)) + ','

  ,@i += 1

  FROM DistinctLocationsCTE

  ORDER BY location_id;

  --Remove comma at the end

  SET @sql = LEFT(@SQL, LEN(@SQL)-1);

  --PRINT @sql

  EXEC (@SQL);

  DROP TABLE #Site;

  --Vadim R.