SQL Query Data parsing help

Question

Post reply

SQL Query Data parsing help

rocky_498

SSCertifiable

Points: 6532

May 18, 2021 at 4:54 am

#3884681

I have sample data (Data is just an example and close to Original data)

Data_Field

Order #123456

Order Date:

17-May-21

Order Total:

$16.31

Recipient:

Sonya Gabi

Order #25699

Order Date:

17-Mah-21

Order Total:

$15.23

Items:

1

Recipient:

James Sc

Shipping Method:

Standard Shipping

Here what I want

Order_No,Order_Date,Order_Total,Recipient

123456,17-May-21,$16.31,Sonya Gabi

25699,17-May-21,$15.23,James Sc

Any advice would be highly appreciated.

Sample SQL attached.

CREATE TABLE dbo.##temp_table
(
id int identity(1,1),
data_field nvarchar(max)

)

INSERT INTO dbo.##temp_table
(
    --id - column value is auto-generated
    data_field
)
Select 'Order #123456'
UNION all
Select 'Order Date:'
UNION all
Select '17-May-21'
UNION all
Select 'Order Total:'
UNION all
Select '$16.31'
UNION all
Select 'Recipient:'
UNION all
Select 'Sonya Gabi'
UNION all
Select 'Order #25699'
UNION all
Select 'Order Date:'
UNION all
Select '17-Mah-21'
UNION all
Select 'Order Total:'
UNION all
Select '$15.23'
UNION all
Select 'Items:'
UNION all
Select '1'
UNION all
Select 'Recipient:'
UNION all
Select 'James Sc'
UNION all
Select 'Shipping Method:'
UNION all
Select 'Standard Shipping'

SELECT * FROM ##temp_table

Viewing 4 posts - 1 through 3 (of 3 total)

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

CREATE TABLE ##temp_table_order#s ( id int NOT NULL PRIMARY KEY, data_field varchar(8000) );
INSERT INTO ##temp_table_order#s
SELECT id, data_field
FROM ##temp_table
WHERE data_field LIKE 'Order #%'

SELECT
    Order_No,
    MAX(CASE WHEN data_field LIKE 'Order Date%' THEN data_field_lead END) AS Order_Date,
    MAX(CASE WHEN data_field LIKE 'Order Total%' THEN data_field_lead END) AS Order_Total,
    MAX(CASE WHEN data_field LIKE 'Recipient%' THEN data_field_lead END) AS Recipient
FROM (
    SELECT tt.id, tt.data_field, tto.data_field AS Order_No,
        LEAD(tt.data_field, 1) OVER(PARTITION BY tto.id ORDER BY tt.id) AS data_field_lead
    FROM ##temp_table tt
    CROSS APPLY (
        SELECT TOP (1) *
        FROM ##temp_table_order#s tto
        WHERE tto.id <= tt.id
        ORDER BY tto.id DESC
    ) AS tto
) AS derived
GROUP BY Order_No

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Thank you, Scott!

I really appreciate your time to help me and write the query.

Looks good!

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

Glad it helped out! Thanks for the feedback.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.