Converting Columns to Rows based on specific column values

  • Hi,

    SQL 2012

    I have some sample data from a query where multiple charges may be applied to a specific product (haulage costs, inspection etc). I need to output the data to produce a row for each product and/by charge where the charge is <> 0.

    For example

    If a current Sales row has 2 charges (both <>0) then I need to convert that single row into 2 rows.

    Existing data sample:

    CREATE TABLE #Results
    (
    Location[nvarchar](10),
    Customer[nvarchar](10),
    Product[nvarchar](20),
    SalesOrderNumber[nvarchar](20),
    PriceNumeric(38, 2),
    CreatedDateDateTime,
    ChargeName[nvarchar](20),
    ChargeAmountnumeric(20,2),
    Charge1numeric(20,2),
    Charge2numeric(20,2),
    Charge3numeric(20,2),
    Charge4numeric(20,2),
    Charge5numeric(20,2)
    );

    INSERT #Results
    VALUES
    ('London', 'Cus1', 'Prod1', '1234', '45.50', '2022-07-05 00:00:00.000', Null, Null, '0.00', '2.54', '4.43', '0.00', '0.00'),
    ('London', 'Cus1', 'Prod2', '5678', '32.90', '2022-07-05 00:00:00.000', Null, Null, '0.00', '8.32', '0.00', '0.00', '0.00'),
    ('New York', 'Cus4', 'Prod7', '4545', '26.90', '2022-07-05 00:00:00.000', Null, Null, '1.00', '0.00', '0.00', '0.00', '7.05')

     

    Current output:

    SQL 2

     

    Required output:

    SQL 1

     

    Also, there isn't a Primary Key in my real query, as rows may contain repeating data in the non charge columns (ie. 'Location' to 'CreatedDate' columns).

    Any ideas?

    Thanks in advance.

     

    • This topic was modified 1 week, 1 day ago by  DerbyNeal.
    • This topic was modified 1 week, 1 day ago by  DerbyNeal.
    • This topic was modified 1 week, 1 day ago by  DerbyNeal.
  • Okay, I totally stole this from an article that Kenneth Fisher wrote (@sqlstudies144). Basically, you use CROSS APPLY to "stack" the repeating groups, and then you query that. Here's my query:

    SELECT [Location],
    Customer,
    Product,
    SalesOrderNumber,
    Price,
    CreatedDate,
    CrossApplied.ChargeName,
    CrossApplied.ChargeAmount
    FROM Results
    CROSS APPLY (VALUES ('Charge1',Charge1)
    ,('Charge2',Charge2),
    ('Charge3',Charge3),
    ('Charge4',Charge4),
    ('Charge5',Charge5)) CrossApplied(ChargeName, ChargeAmount)
    WHERE CrossApplied.ChargeAmount>0;

    Note the columns in the VALUES() group. The first I called "ChargeName", and the second "ChargeAmount", and that virtual table created by the CROSS APPLY I ever so cleverly called "CrossApplied" to make it obvious where the funky data was coming from. (You can name it anything you want... Originally I named it "ca", but I figured that wasn't terribly clear, so I fixed it.

    Then I refer to the columns returned by the CROSS APPLY function by the SELECT part of the statement, and filter them.

    Hope it helps!

    If you have questions, feel free to ask.

    Pieter

  • another option is "unpivot"

    SELECT

    [Location]

    ,Customer

    ,Product

    ,SalesOrderNumber

    ,Price

    ,CreatedDate

    ,ChargeName

    ,ChargeAmount

    FROM

    (

    SELECT

    [Location]

    ,Customer

    ,Product

    ,SalesOrderNumber

    ,Price

    ,CreatedDate

    ,Charge1

    ,Charge2

    ,Charge3

    ,Charge4

    ,Charge5

    FROM

    #Results

    ) x

    UNPIVOT

    (

    ChargeAmount FOR ChargeName IN

    (

    Charge1

    ,Charge2

    ,Charge3

    ,Charge4

    ,Charge5

    )

    )AS unpvt;

  • Many thanks for your prompt responses. I am testing it now. Looks good.

    • This reply was modified 1 week ago by  DerbyNeal.
  • To handle the scenario where additional charge columns could be added to the table, "charge6, charge7" etc, you could do the below to dynamically unpivot any columns that match your column name string

    drop table if exists ##Results
    go
    CREATE TABLE ##Results
    (
    Location[nvarchar](10),
    Customer[nvarchar](10),
    Product[nvarchar](20),
    SalesOrderNumber[nvarchar](20),
    Price Numeric(38, 2),
    CreatedDate DATETIME,
    Charge1 numeric(20,2),
    Charge2 numeric(20,2),
    Charge3 numeric(20,2),
    Charge4 numeric(20,2),
    Charge5 numeric(20,2)
    );

    INSERT ##Results
    VALUES
    ('London', 'Cus1', 'Prod1', '1234', '45.50', '2022-07-05 00:00:00.000', '0.00', '2.54', '4.43', '0.00', '0.00'),
    ('London', 'Cus1', 'Prod2', '5678', '32.90', '2022-07-05 00:00:00.000', '0.00', '8.32', '0.00', '0.00', '0.00'),
    ('New York', 'Cus4', 'Prod7', '4545', '26.90', '2022-07-05 00:00:00.000', '1.00', '0.00', '0.00', '0.00', '7.05')




    DECLARE @ColsToPivot NVARCHAR(255) = ''
    DECLARE @ColSearch NVARCHAR(255) = 'charge%'

    SELECT @ColsToPivot = STRING_AGG(c.name,',')
    FROM tempdb.sys.tables t
    JOIN tempdb.sys.columns c on c.object_id = t.object_id
    WHERE t.name = '##Results'
    AND c.name like @ColSearch

    DECLARE @SqlStatement NVARCHAR(MAX)

    SET @SqlStatement = N'
    SELECT
    UnPivotTable.Location,
    UnPivotTable.Customer,
    UnPivotTable.Product,
    UnPivotTable.SalesOrderNumber,
    UnPivotTable.Price,
    UnPivotTable.CreatedDate,
    UnPivotTable.ChargeName,
    UnPivotTable.ChargeValue
    FROM
    (
    SELECT
    *
    FROM ##Results
    ) Charges
    UNPIVOT
    (
    ChargeValue FOR Chargename IN
    ('+@ColsToPivot+')
    ) AS UnPivotTable

    '
    EXEC(@SqlStatement)

    • This reply was modified 11 hours, 52 minutes ago by  planetmatt.
    • This reply was modified 11 hours, 52 minutes ago by  planetmatt.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply