Search code examples
sqlsql-servert-sqlsql-server-2012cursor

TSQL - Create view based on table rows and cell values

I'm stuck with below left table structure (SQL Server 2012) to create a view or function that will output the right table in the screenshot:

Input and Output

The TestOutput table contains data starting with RowNumber 10 (Step0_Name1) for every ID. In the real scenario there are more steps than I listed here as an example.

I'm hoping for some logic to get all pairs written lke Step0_Name1 and Step0_Name1_Status be it by name/number recognition or row numbering.

I'm guessing cursors will be needed to create some kind of loop logic.

Unfortunately sqlfiddle.com throws an error so I pasted the schema in here:

Create Test:

CREATE TABLE [dbo].[Test](
    [ID] [nchar](5) NOT NULL,
    [Version] [smallint] NOT NULL,
    [RowNumber] [int] NOT NULL,
    [COLUMN1] [nvarchar](max) NULL,
    [COLUMN2] [nvarchar](max) NULL,
    [COLUMN3] [nvarchar](max) NULL,
    [COLUMN4] [numeric](15, 3) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(   [ID] ASC,
    [RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Insert Test:

INSERT INTO [dbo].[Test]
           ([ID] ,[Version] ,[RowNumber],[COLUMN1],[COLUMN2],[COLUMN3],[COLUMN4])
Values 
('00010',1, 1, 'Sheet', 'Part',             'A',            NULL),
('00010',1, 2, 'Sheet', 'Name',             'PartName',     NULL),
('00010',1, 3, 'Sheet', 'Price',            NULL,           1.470),
('00010',1, 4, 'Sheet', 'Step0_SOP',        '2015 CW 09',   NULL),
('00010',1, 5, 'Sheet', 'Step1_SOP',        '2015 CW 10',   NULL),
('00010',1, 6, 'Sheet', 'Step2_SOP',        '2015 CW 11',   NULL),
('00010',1, 7, 'Sheet', 'Step0_Status',     'pending',      NULL),
('00010',1, 8, 'Sheet', 'Step1_Status',     'frozen',       NULL),
('00010',1, 9, 'Sheet', 'Step2_Status',     'released',     NULL),
('00010',1, 10,'Sheet', 'Step0_Name1',      'Auser',        NULL),
('00010',1, 11,'Sheet', 'Step0_Name2',       NULL,          NULL),
('00010',1, 12,'Sheet', 'Step0_Name3',      'Buser',        NULL),
('00010',1, 13,'Sheet', 'Step0_Name1_Status','started',     NULL),
('00010',1, 14,'Sheet', 'Step0_Name2_Status',NULL,          NULL),
('00010',1, 15,'Sheet', 'Step0_Name3_Status','pending',     NULL),
('00010',1, 16,'Sheet', 'Step1_Name1',      'Wuser',        NULL),
('00010',1, 17,'Sheet', 'Step1_Name2',      'Cuser',        NULL),
('00010',1, 18,'Sheet', 'Step1_Name3',      'Duser',        NULL),
('00010',1, 19,'Sheet', 'Step1_Name1_Status','released',    NULL),
('00010',1, 20,'Sheet', 'Step1_Name2_Status','pending',     NULL),
('00010',1, 21,'Sheet', 'Step1_Name3_Status','released',    NULL),

('00020',1, 1, 'Sheet', 'Part',             'B',            NULL),
('00020',1, 2, 'Sheet', 'Name',             'PartName',     NULL),
('00020',1, 3, 'Sheet', 'Price',            NULL,           2.190),
('00020',1, 4, 'Sheet', 'Step0_SOP',        '2016 CW 19',   NULL),
('00020',1, 5, 'Sheet', 'Step1_SOP',        '2016 CW 20',   NULL),
('00020',1, 6, 'Sheet', 'Step2_SOP',        '2015 CW 21',   NULL),
('00020',1, 7, 'Sheet', 'Step0_Status',     'released',     NULL),
('00020',1, 8, 'Sheet', 'Step1_Status',     'frozen',       NULL),
('00020',1, 9, 'Sheet', 'Step2_Status',     'pending',      NULL),
('00020',1, 10,'Sheet', 'Step0_Name1',      'Xuser',        NULL),
('00020',1, 11,'Sheet', 'Step0_Name2',       NULL,          NULL),
('00020',1, 12,'Sheet', 'Step0_Name3',      'Wuser',        NULL),
('00020',1, 13,'Sheet', 'Step0_Name1_Status','started',     NULL),
('00020',1, 14,'Sheet', 'Step0_Name2_Status',NULL,          NULL),
('00020',1, 15,'Sheet', 'Step0_Name3_Status','pending',     NULL),
('00020',1, 16,'Sheet', 'Step1_Name1',      'Auser',        NULL),
('00020',1, 17,'Sheet', 'Step1_Name2',      'Cuser',        NULL),
('00020',1, 18,'Sheet', 'Step1_Name3',      'Buser',        NULL),
('00020',1, 19,'Sheet', 'Step1_Name1_Status','frozen',      NULL),
('00020',1, 20,'Sheet', 'Step1_Name2_Status','pending',     NULL),
('00020',1, 21,'Sheet', 'Step1_Name3_Status','released',    NULL)

Create TestOutput:

CREATE TABLE [dbo].[TestOutput](
    [ID] [nchar](5) NOT NULL,
    [StepCount] [int] NULL
    [StepNo] [int] NULL,
    [Name] [nvarchar](max) NULL,
    [Status] [nvarchar](max) NULL   
    )

Insert TestOutput:

INSERT INTO [dbo].[TestOutput]   ([ID] ,[StepCount] ,[StepNo],[Name],[Status])
Values 
('00010',1, 0, 'Auser', 'started'),
('00010',2, 0, NULL, NULL),
('00010',3, 0, 'Buser', 'pending'),
('00010',1, 1, 'Wuser', 'released'),
('00010',2, 1, 'Cuser', 'pending'),
('00010',3, 1, 'Duser', 'released'),

('00020',1, 0, 'Xuser', 'started'),
('00020',2, 0, NULL, NULL),
('00020',3, 0, 'Wuser', 'pending'),
('00020',1, 1, 'Auser', 'frozen'),
('00020',2, 1, 'Cuser', 'pending'),
('00020',3, 1, 'Buser', 'released')

I hope you can show me a way how this could be solved.

Thank you very much.

Solution

  • There is no need for cursor.

    You could first get StepNo, then calculate StepCount using windowed function and join results:

    SELECT s1.ID,
       s1.StepCount,
       s1.StepNo,
       [Name]     = s1.Column3,
       [Status]   = s2.Column3 
FROM (SELECT *,
        [StepCount] =  ROW_NUMBER() OVER (PARTITION BY ID, StepNo ORDER BY RowNumber)
      FROM (SELECT *, 
               [StepNo] = SUBSTRING(Column2, 5, CHARINDEX('_', Column2)-5)
            FROM Test
            WHERE Column2 LIKE 'Step%_Name%' 
              AND Column2 NOT LIKE '%Status') AS sub) AS s1
JOIN (SELECT *, 
        [StepCount] =  ROW_NUMBER() OVER (PARTITION BY ID, StepNo ORDER BY RowNumber)
      FROM (SELECT *, 
             [StepNo] = SUBSTRING(Column2, 5, CHARINDEX('_', Column2)-5)
            FROM Test
            WHERE Column2 LIKE 'Step%_Name%_Status') AS sub) AS s2
  ON s1.ID = s2.ID
 AND s1.StepCount = s2.StepCount
 AND s1.StepNo = s2.StepNo
ORDER BY ID, StepNo, StepCount;

    LiveDemo

    Output:

    ╔═══════╦═══════════╦════════╦═══════╦══════════╗
║  ID   ║ StepCount ║ StepNo ║ Name  ║  Status  ║
╠═══════╬═══════════╬════════╬═══════╬══════════╣
║ 00010 ║         1 ║      0 ║ Auser ║ started  ║
║ 00010 ║         2 ║      0 ║       ║          ║
║ 00010 ║         3 ║      0 ║ Buser ║ pending  ║
║ 00010 ║         1 ║      1 ║ Wuser ║ released ║
║ 00010 ║         2 ║      1 ║ Cuser ║ pending  ║
║ 00010 ║         3 ║      1 ║ Duser ║ released ║
║ 00020 ║         1 ║      0 ║ Xuser ║ started  ║
║ 00020 ║         2 ║      0 ║       ║          ║
║ 00020 ║         3 ║      0 ║ Wuser ║ pending  ║
║ 00020 ║         1 ║      1 ║ Auser ║ frozen   ║
║ 00020 ║         2 ║      1 ║ Cuser ║ pending  ║
║ 00020 ║         3 ║      1 ║ Buser ║ released ║
╚═══════╩═══════════╩════════╩═══════╩══════════╝

    The same with CTE:

    ;WITH cte_name AS
(
  SELECT *,
        [StepCount] =  ROW_NUMBER() OVER (PARTITION BY ID, StepNo ORDER BY RowNumber)
  FROM (SELECT *, 
          [StepNo] = SUBSTRING(Column2, 5, CHARINDEX('_', Column2)-5)
       FROM #Test
       WHERE Column2 LIKE 'Step%_Name%' 
         AND Column2 NOT LIKE '%Status') AS sub
), cte_status AS
(
  SELECT *, 
        [StepCount] =  ROW_NUMBER() OVER (PARTITION BY ID, StepNo ORDER BY RowNumber)
  FROM (SELECT *, 
             [StepNo] = SUBSTRING(Column2, 5, CHARINDEX('_', Column2)-5)
        FROM #Test
        WHERE Column2 LIKE 'Step%_Name%_Status') AS sub
)
SELECT s1.ID,
       s1.StepCount,
       s1.StepNo,
       [Name]     = s1.Column3,
       [Status]   = s2.Column3 
FROM cte_name s1
JOIN cte_status s2
  ON s1.ID = s2.ID
 AND s1.StepCount = s2.StepCount
 AND s1.StepNo = s2.StepNo
ORDER BY ID, StepNo, StepCount;