How to transpose (partially) table fields

[Val Lusis]

I'm trying to partially transpose a table into a delimited file. I have the bcp figured out, but would like some help with transposing fields.

I have table this way (for purpose of simplicity I am showing only a few fields, in the real table I have quite a bit more) :

MyId,A_Qtr1,A_Qtr2,A_Qtr3,A_Qtr4,B_Qtr1,B_Qtr2,B_Q tr3,B_Qtr4,Field1,Field2,Field3

How would I approach it with select query so I'd get four (4) rows out of each row in the table that looks this way:

MyId,Qtr,QtrDataA,QtrDataB,Field1,Field2,Field3

Qtr would be a new field indicating the quarter (1-4), and QtrDataA, QtrDataB would have data from A_Qtr1 through A_Qtr4 and B_Qtr1 through B_Qtr5 fields.

Thanks,

Val

[Val Lusis]

This turns out to be easier than I thought (code below), now I need to figure out how to get those headers to my delimited file out of that select statement.

From the table I get them out as follows, it adds an extra pipe at the end, but that's fine (I stumbled upon this solution on some website, but have been sleeping many night since then and cannot remember who and where it was, sorry):

-- Export Headers from MyTable
DECLARE @sql_MyTableHeaders VARCHAR(8000)
SELECT @sql_MyTableHeaders = 'bcp "SELECT column_name from [MyDB].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=''MyTable''" queryout e:\CSV_Exports\MyTableHeaders.csv -c -r"|" -T'
EXEC "MyDB"..xp_cmdshell @sql_MyTableHeaders
GO
 

Here is the solution to my original question:

(SELECT 
    MyId,
    1 as Qtr,
    A_Qtr1 as QtrDataA,
    B_Qtr1 as QtrDataB,
    C_Qtr1 as QtrDataC,
    D_Qtr1 as QtrDataD,
    E_Qtr1 as QtrDataE
FROM dbo.MyTable)
UNION
(SELECT 
    MyId,
    2 as Qtr,
    A_Qtr2 as QtrDataA,
    B_Qtr2 as QtrDataB,
    C_Qtr2 as QtrDataC,
    D_Qtr2 as QtrDataD,
    E_Qtr2 as QtrDataE
FROM dbo.MyTable)
UNION
(SELECT 
    MyId,
    3 as Qtr,
    A_Qtr3 as QtrDataA,
    B_Qtr3 as QtrDataB,
    C_Qtr3 as QtrDataC,
    D_Qtr3 as QtrDataD,
    E_Qtr3 as QtrDataE
FROM dbo.MyTable)
UNION
(SELECT 
    MyId,
    4 as Qtr,
    A_Qtr4 as QtrDataA,
    B_Qtr4 as QtrDataB,
    C_Qtr4 as QtrDataC,
    D_Qtr4 as QtrDataD,
    E_Qtr4 as QtrDataE
FROM dbo.MyTable)
 
 

Thanks,

Val