Sql-server – T-SQL Splitting rows into multiple rows

sql servert-sql

I have a unique request here. I have to split rows for each ID into separate rows, but moving sets of columns into separate rows. Kind of hard to explain with just that so here is a visual representation:

I have to get something like this:

            Column A    |   Column B    |   Column C    |   Column D
    ------------------------------------------------------------
    1       123         |    1abc       |     def       |      ghi
    ------------------------------------------------------------
    2       123         |    2abc       |     def       |      ghi
    ------------------------------------------------------------
    3       123         |    3abc       |     def       |      ghi
    ------------------------------------------------------------
    4       456         |    1abc       |     def       |      ghi
    ------------------------------------------------------------
    5       456         |    2abc       |     def       |      ghi

To look like this:

            Column A    |   Column B    |   Column C    |   Column D
    ---------------------------------------------------------------------
    1       123         |               |               |      
    ---------------------------------------------------------------------
    2                   |    1abc       |               |      
    ---------------------------------------------------------------------
    3                   |    2abc       |               |      
    ---------------------------------------------------------------------
    3                   |    3abc       |               |      
    ---------------------------------------------------------------------
    4                   |               |     def       |      
    ---------------------------------------------------------------------
    5                   |               |               |      ghi
    ---------------------------------------------------------------------
    6       456         |               |               |      
    ---------------------------------------------------------------------
    7                   |    1abc       |               |      
    ---------------------------------------------------------------------
    8                   |    2abc       |               |      
    ---------------------------------------------------------------------
    9                   |               |     def       |      
    ---------------------------------------------------------------------
    10                  |               |               |      ghi

I have a slight understanding that I will have to loop through the recordset then possibly use a CTE. This would then move onto the next ID in Column A and on.

Any ideas would be appreciated!

Best Answer

WITH cte AS (
SELECT *, DENSE_RANK() OVER (ORDER BY ColumnA) dr
FROM source
),
cte2 AS (
SELECT DISTINCT ColumnA, NULL ColumnB, NULL ColumnC, NULL ColumnD, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, ColumnB, NULL, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL , NULL, ColumnC, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, NULL, NULL, ColumnD, dr
FROM cte
)
SELECT ColumnA, ColumnB, ColumnC, ColumnD 
FROM cte2
ORDER BY dr, ColumnA DESC, ColumnB DESC, ColumnC DESC, ColumnD DESC

fiddle

PS. Edit final ORDER BY clause (except dr first) as you need.

Related Solutions

SQL Server – Splitting CSV Column from SELECT Query into Multiple Columns

Making use of Jeff Moden's Tally-Ho! CSV splitter from here:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
WITH E1(N) AS (
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
       ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
            SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() 
                                                        OVER (ORDER BY (SELECT NULL)) FROM E4
        ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just
                     -- once for each delimiter)
            SELECT 1 UNION ALL
            SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
        ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
            SELECT s.N1,
                   ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
            FROM cteStart s
        )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final
     -- element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
go

We can code the solution as an apply against Jeff's function and a pivot like so:

with data as (
    select Code,Location,Quantity,Store from ( values
        ('L698-W-EA',          NULL,                                      2, 'A')
       ,('L82009-EA',          'A1K2, A1N2, C4Y3, CBP2',                  2, 'A')
       ,('L80401-A-EA',        'A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20',    17,'A')
       ,('CWD2132W-BOX-25PK',  'A-AISLE',                                 1, 'M')
       ,('GM22660003-EA',      'B1K2',                                    1, 'M')
    )data(Code,Location,Quantity,Store)
)
,shredded as (
    select Code,Location,Quantity,Store,t.*
    from data
    cross apply [dbo].[DelimitedSplit8K](data.Location,',') as t
)
select 
    pvt.Code,pvt.Quantity,pvt.Store
   ,cast(isnull(pvt.[1],' ') as varchar(8)) as Loc1
   ,cast(isnull(pvt.[2],' ') as varchar(8)) as Loc2
   ,cast(isnull(pvt.[3],' ') as varchar(8)) as Loc3
   ,cast(isnull(pvt.[4],' ') as varchar(8)) as Loc4
   ,cast(isnull(pvt.[5],' ') as varchar(8)) as Loc5 
   ,cast(isnull(pvt.[6],' ') as varchar(8)) as Loc6
from shredded
pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5],[6])) pvt;
;
go

yielding this:

Code              Quantity    Store Loc1     Loc2     Loc3     Loc4     Loc5     Loc6
----------------- ----------- ----- -------- -------- -------- -------- -------- --------
L698-W-EA         2           A                                                   
L82009-EA         2           A     A1K2      A1N2     C4Y3     CBP2              
L80401-A-EA       17          A     A1S2      SHIP     R2F1     CBP5     BRP      BRP1-20
CWD2132W-BOX-25PK 1           M     A-AISLE                                       
GM22660003-EA     1           M     B1K2

SQL Server – Splitting a Result Row into Multiple Rows

As Mark mentioned, UNPIVOT would be a good choice here. I often find it's easier to throw the original query into a temp table then unpivot that. You could use that approach, CTEs or views depending on performance. All the datatypes need to line up for it to work, so I'm casting everything to MONEY in my example, something like this:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
       vs.MonthName AS Month, 
       vs.Region, 
       vs.Category, 
       CAST (MIN (a.TotalAmount) AS MONEY) AS ActualAmount, 
       CAST (MIN (a.TotalCases)  AS MONEY) AS ActualCases, 
       CAST (MIN (ve.BudgetAmount) AS MONEY) AS BudgetAmount,
       CAST (MIN (ve.BudgetCases)  AS MONEY) AS BudgetCases
INTO #tmp
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals 
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries 
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName, 
        vs.Region, 
        vs.Category;
GO


SELECT [Month], Region, Category, ActualAmount, ActualCases, BudgetAmount, BudgetCases
FROM #tmp
UNPIVOT ( [Type] For [Value] In ( [ActualAmount], [ActualCases], [BudgetCases], [BudgetAmount] ) ) upvt

Best Answer

Related Solutions

SQL Server – Splitting CSV Column from SELECT Query into Multiple Columns

SQL Server – Splitting a Result Row into Multiple Rows

Related Question