sql data muniplication help

• imranazam

  SSC Enthusiast

  Points: 189

  More actions

  November 6, 2017 at 5:03 am

  #324403

  hi guys

  hope everyone is well,

  I need some help

  I need to manipulate some data so I can produce a performance report

  I have the data below
  

  I need to rearrange this data to look like the below
  

  how can I do this? my sql knowledge isn't very good.

  the table this data is coming from is called Data_Table

  thank you for any help provides

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 6, 2017 at 5:16 am

  #1966746

  Welcome to SSC. When posting data questions, it best to provide DDL and consumable sample data for other users. We don't have access to your server, or data, thus you need to supply it in a format that can be quickly and easily used by others. I've done this for you for this one:

  USE Sandbox;

  GO

  --CREATE TABLE statement (DDL)

  CREATE TABLE #Sample

    (ID tinyint,

    MainDate date,

    [System] varchar(5),

    Comment bit, --These are all NULL so I have no idea what the datatype is

    [Type] tinyint,

    LCount tinyint,

    NLCount tinyint,

    FCount tinyint,

    DepID tinyint);

  GO

  --INSERT sample data into table

  INSERT INTO #Sample

  VALUES

    (11,'20171105','red',NULL,1,5,10,3,5),

    (22,'20171105','red',NULL,2,4,1,4,7);

  GO

  --Display Data

  SELECT *

  FROM #Sample;

  GO

  --Clean up

  DROP TABLE #Sample;

  GO

  I'll start looking at a solution for you now, but this'll help others answer as well (who may get to the end result faster than myself).

  Thom~

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

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 6, 2017 at 5:22 am

  #1966748

  Ok, for a solution, this should get you what you're after. The method is called Cross Pivoting (because you use a CROSS APPLY to Pivot your data). If you have any questions on how it works, please do ask.

  SELECT S.ID, S.MainDate,

     S.[System],

     S.Comment,

     S.[Type],

     C.F AS Fields,

     CASE C.F WHEN 'L Count' THEN S.LCount

         WHEN 'F Count' THEN S.FCount

        WHEN 'N L Count' THEN S.NLCount END AS [Values],

     S.DepID

  FROM #Sample S

    CROSS APPLY (VALUES ('L Count'),('N L Count'),('F Count')) C(F);

  Thom~

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

• Thomas Rushton

  SSC-Insane

  Points: 22648

  More actions

  November 6, 2017 at 5:23 am

  #1966749

  Hah.  I was just working through this myself.

  This seems to work, but using a table variable rather than your temporary table, Thom A.

  DECLARE @DataTable TABLE
(
  id INTEGER,
  maindate DATE,
  system VARCHAR(10),
  comment VARCHAR(10),
  type INTEGER,
  lcount INTEGER,
  nlcount INTEGER,
  fcount INTEGER,
  depid INTEGER
);
INSERT INTO @DataTable
VALUES
(11, '2017-11-05', 'red', NULL, 1, 5, 20, 3, 5),
(22, '2017-11-05', 'red', NULL, 2, 4, 1, 4, 7);

  SELECT id,
   maindate,
   system,
   comment,
   x.Fields,
   x.[Values],
   depid
FROM @DataTable
  CROSS APPLY
(
  VALUES
   ('lcount', lcount),
   ('nlcount', nlcount),
   ('fcount', fcount)
) x (Fields, [Values]);

  --edit: tweaked cross apply field names to match your requirement.  Note use of square brackets around keyword

  Technique lifted shamelessly from Kenneth Fisher's blog post "unpivot a table using cross apply"

  Thomas Rushton
  blog: https://thelonedba.wordpress.com