How to convert table to flat file with each field on new row

Question

I have a pipe delimited file with user and address information in the following format:

userid|name|street|city|state|zip
123456|fred|123 apple st|Navada|MO|64772

I to output the information another flat file in the following format:

*** DOCUMENT BOUNDARY ***
FORM=LDUSER
.USER_ID.   123456
.USER_ADDR1_BEGIN.
.STREET.   123 apple st
.CITY.   Nevada
.STATE.   MO
.ZIP.   64772
.USER_ADDR1_END.

Is there a generic SQL or PowerShell script that would do such a conversion? I've searched with the phrases "convert columns to rows", "pivot table to flat file", etc. but haven't found anything helpful. I'm not sure exactly how to call the output file's format, dot/row delimited? hierarchical flat file?

Answer 1

There is no generic method for achieving this in SQL server as far as I know, but if you manage to get the data to a table then the following custom query will help you render the data as a single column which you could export afterwards

CREATE TABLE #temp (UserID INT, Name VARCHAR(40), Street VARCHAR(40), City VARCHAR(40), State VARCHAR(40), Zip INT)

INSERT INTO #temp VALUES
(101,'Kevin Joy','Ap #659-7998 Integer St.','Hinckley','4863 Ligula. Av.',98417),
(102,'Peter Romell','979-4806 Massa Road','Knoxville','4568 A Street',90825),
(103,'Mariam Sid','P.O. Box 547, 8395 Ultrices Av.','Beervelde','Ap #758-6207 Non, Av.',486291),
(104,'Roberta Kotun','9482 Habitant Ave','Northumberland','Ap #620-957 Rutrum Av.',476177)

;WITH CTE AS (

SELECT 
    CAST(UserID AS VARCHAR(MAX)) AS UserID
    ,Name
    ,Street
    ,City
    ,[State]
    ,CAST(Zip AS VARCHAR(MAX)) AS Zip
FROM #temp )

SELECT '*** DOCUMENT BOUNDARY ***' AS [Data]
UNION ALL
SELECT 'FORM=LDUSER'
UNION ALL
SELECT T.Result
FROM CTE
CROSS APPLY (VALUES ('.USER_ID.'+' '+UserID,'Col1'),('.USER_ADDR1_BEGIN.','Col2'),('.STREET.'+' '+Street,'Col3'),('.CITY.'+' '+City,'Col4'),('.STATE.'+' '+[State],'Col5'),('.ZIP.'+' '+Zip,'Col6'),('.USER_ADDR1_END.','Col7')) AS T(Result,Value)

Result of the query is as below,

*** DOCUMENT BOUNDARY ***
FORM=LDUSER
.USER_ID. 101
.USER_ADDR1_BEGIN.
.STREET. Ap #659-7998 Integer St.
.CITY. Hinckley
.STATE. 4863 Ligula. Av.
.ZIP. 98417
.USER_ADDR1_END.
.USER_ID. 102
.USER_ADDR1_BEGIN.
.STREET. 979-4806 Massa Road
.CITY. Knoxville
.STATE. 4568 A Street
.ZIP. 90825
.USER_ADDR1_END.
.USER_ID. 103
.USER_ADDR1_BEGIN.
.STREET. P.O. Box 547, 8395 Ultrices Av.
.CITY. Beervelde
.STATE. Ap #758-6207 Non, Av.
.ZIP. 486291
.USER_ADDR1_END.
.USER_ID. 104
.USER_ADDR1_BEGIN.
.STREET. 9482 Habitant Ave
.CITY. Northumberland
.STATE. Ap #620-957 Rutrum Av.
.ZIP. 476177
.USER_ADDR1_END.