How to output data from rows in different tables into one text file with each row having different value types

1

Hi i have a work task to output data into a text file but each row does not have the same meaning ( apologies if this isn't clear but im finding it hard to articulate my problem)

Basically a very simplistic example is below where i need to use data form both the policy and drivers table and output into a single text file in the format shown. Note columns in policy table means something different to columns in drivers table but the rows from both have a common column polnum that is used to group the records in the text file. Also my full task contains data with lots of polnums and lots more tables

So my question is, how do i go about creating a text file in this way? i know there are various ways of outputting full sql tables to text but for this i will need to somehow handle each row output differently

any help on how i could do this would be much appreciated :slight_smile:

Thanks

Keith

create table policy_table
(polnum varchar(10),
 col_a varchar(5) 
)
insert into policy_table
values
( 'xxx', 'p1'),
( 'xxx', 'p2')

create table drivers_table
(polnum varchar(10),
 drivenum tinyint,
 col_x varchar(5),
 col_y varchar(3),
 col_z varchar(4)
)
insert into drivers_table
values
( 'xxx', 1, 'd1a', 'd1b', 'd1c' ),
( 'xxx', 2, 'd2a' ,null, 'd2c' )

The Text file i need is like this (e.g output.txt)
x|policy_row|p1|p2| |
x|driver_row_1|d1a|d1b|d1c
x|driver_row_2|d1a| |d1c

2

Can you post usable data, such as #policy and #drivers CREATE TABLE and INSERT statements, rather than just a "splat" of data on the screen? (We can't write code against a "splat" :slight_smile: )

3

i 've edited the post now to include the syntax to create the tables

Thanks

4

Hi does anyone have any advice for me on this problem?

5

It looks like you want to pivot the policy table into:

+-----+----+----+
| xxx | p1 | p2 |
+-----+----+----+

and then union that together with drivers table.

But I see that there are two "empty columns" on the policy row.

+-----+----+----+----+----+
| xxx | p1 | p2 |    |    |
+-----+----+----+----+----+

Are those empty columns there to align with the number of columns in the drivers rows, or does a policy row always have a max of 4 values (p1, p2, p3, p4)?
What if there are 5 or 6 policy values (p5, p6)?

Do drivers only ever have a, b, and c columns? Are there ever d or more?

In the output does it need to look like this:

x|policy_row|p1|p2||
x|driver_row_1|d1a|d1b|d1c
x|driver_row_2|d1a||d1c

or should it look like this:

x|policy_row|p1|p2
x|driver_row_1|d1a|d1b|d1c
x|driver_row_2|d1a||d1c
6

This will gen the results you want. You would just need to output this result to a text file using, for example, bcp or perhaps sqlcmd.

;WITH cte_polnums AS (
    SELECT DISTINCT polnum
    FROM #policy_table
)
SELECT string
FROM (
    SELECT polnum, 0 AS drivenum,
        polnum + '|policy_row|' + (
            SELECT ISNULL(col_a, ' ') + '|'
            FROM #policy_table
            WHERE polnum = cp.polnum
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)') AS string
    FROM cte_polnums cp
    UNION ALL
    SELECT polnum, drivenum,
        polnum + '|driver_row|' + ISNULL(col_x, ' ') + '|' + ISNULL(col_y, ' ') + '|' + ISNULL(col_z, ' ') + '|' AS string 
    FROM #drivers_table
) AS derived
ORDER BY polnum, drivenum
7

Hi .Thanks for your reply

Apologies . i might not have not explained my problem too well. The output is supposed to represent data rows from a variety of tables ( in my real life scenario) e.g a main policy table , driver table, vehivle table,address table . and there is no concept of columns in the text output. The idea is that each row in the text output contains different types of data values seprated by a pipe dilmiter but have a value in common linking rows together. each row type can have different number of fields

Another example of output could be:

policy x|driver row 1|21|Male|20220312
policy x|driver row 2|34|Female|20220312
policy x|address row|2 uptoon road|London|L25 7gy|
policy x|vehicle row|mazda 3|manual|20221201|18000|saphire blue
policy y|driver row 1|21|Male|20220312
policy y|address row| |Manchester|M5 7fgt|
policy y|vehicle row|BMW|automatic| |34000|white

My problem is that im not sure how i can output data to a text file this way when each row of text output comes from a different table.

8

@ScottPletcher Thanks for your reply, that seems to work.

Hopefully this can work for my real world example with lots of row types

Just a question, im not familiar with the xml syntax. Can you explian to me what the

FOR XML PATH(''), TYPE).value('.', 'varchar(max)') AS string

line does? also why is this line is not needed in the second select statement below the UNION ALL ?

9

XML provides a "gimmick" to do data concatenation across multiple rows, usually related rows.

That technique / "trick" is not needed in the second query because all the values are in a single row; XML is really only needed when combining multiple rows.