How can I convert a row with many columns to a table with two columns (fieldname and value) and many rows?

Question

Since I hate asking for specific solutions, here's the background for my question in case I'm approaching it the wrong way. Skip to the bold line if you want to skip the background and get right to the question. I am creating a PDF form that will be prepopulated with data from an SQL database. In order to do this, I need to export the data from the database into an XFDF file, which I can then easily merge with the blank PDF form to fill in the values.

An XFDF file has this format:

<fields>
    <field name="FirstName">
        <value>John</value>
    </field>
    <field name="LastName">
        <value>Smith</value>
    </field>
</fields>

Where "FirstName" and "LastName" correspond to names of fields on the PDF form, and "John" and "Smith" are the corresponding values from the database to populate the form with.

So my goal is to create the XFDF file, and have the names of the fields on the PDF correspond to the names of the columns in the table. My current approach is to use a SELECT FOR XML EXPLICIT statement. To use that, I first need to select the data into the "Universal Table" format that the for xml explicit statement requires, which would have a separate row for each column in my original data table. I could manually go through each field, writing a separate select statement for each column- so for example, the FirstName column part would look something like:

SELECT  2               AS Tag,
        1               AS Parent,
        null            AS [fields!1],
        FirstName       AS [fields!2!name],
        D.FirstName     AS [value!3!]
From    Data AS D

(I am not sure that's exactly the right syntax for the select for XML statement, but it should be something like that)

If I did a select statement like that for each column in my original table (plus one for the root level "fields" element, and then unioned them together, it should create the table I need to then do the SELECT FOR XML EXPLICIT statement.

However, there are a ton of fields on this form (government agencies! Yeesh!) and I'd prefer some sort of way to do this programmatically.

So now that I've given the background, here's my actual question. If you read the background and think my approach sucks, let me know.

I need a way to take a table containing a row like this:

+-----------+----------+-----+
| FirstName | LastName | Etc |
+-----------+----------+-----+
| John      | Smith    | 123 |
+-----------+----------+-----+

and construct a new table from it that looks like this:

+-----------+-------+
| FieldName | Value |
+-----------+-------+
| FirstName | John  |
| LastName  | Smith |
| Etc       | 123   |
+-----------+-------+

If this were valid, I'd do it like this:

SELECT  2               AS Tag,
        1               AS Parent,
        null            AS [fields!1],
        C.column_name       AS [fields!2!name],
        D.{C.column_name}       AS [value!3!]
From        Data AS D, information_schema.columns AS C 
WHERE   C.table_name = 'Data'   

Obviously that is not possible, and you can't make the column being selected variable. I looked at this question ( Use variable Column name in Select statement on SQL server 2008 ) and the top answer might be promising but it ends with "But it's all pretty horrible, IMHO." Before embarking down that road, I'd like to look into solutions that aren't "pretty horrible". Any suggestions?

Answer 1

You can use UNPIVOT in MS SQL 2008 the problem is that you need to have the same datatype and collation in all the columns, or do a convert like in my example below. The result should be exactly like the one you want

SELECT seq,ts
FROM 
    (select 

  convert(nvarchar(200),FirstName) COLLATE Latin1_General_CI_AS  as FirstName,
  convert(nvarchar(200),LastName) COLLATE Latin1_General_CI_AS  as LastName,
  convert(nvarchar(200),Etc) COLLATE Latin1_General_CI_AS  as Etc,

    from youPersonTable P 
       where  id = 1
) p
UNPIVOT
    (ts FOR Seq IN 
        (FirstName,LastName,Etc)
) AS unpvt

Answer 2

Another option would be to work with two tables plus a temp table:

tblEmployee
tblXTABEmployee --This table basically swaps the rows for columns.
tmpXTABEmployee

You can get all the column names in tblEmployee by using this command:

EXEC sp_help tblEmployee

Then you could use a cursor to populate your tblXTABEmployee. Feel fee to ask if you want more details.

UPDATE: You would have to create a tmpXTABEmployee that you would select all the column names into using sp_help, and then use a cursor something like this (please note that I haven't tested this):

declare @ColumnName nvarchar(255)
declare @SQLString nvarchar(4000)
declare columnNameCursor cursor for

SELECT     ColumnName
FROM       tmpXTABEmployee
GROUP BY ColumnName
ORDER BY ColumnName

OPEN columnNameCursor

FETCH NEXT FROM columnNameCursor INTO @ColumnName

BEGIN TRY
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQLString = 'INSERT INTO dbo.tblXTABEmployee (FieldName, DataValue) VALUES (' + @ColumnName + ', ' + dbo.tmpXTABEmployee.DataValue + ')'
    EXEC (@SQLString)
    FETCH NEXT FROM columnNameCursor INTO @ColumnName
END
END TRY 
BEGIN CATCH
DECLARE @msg VARCHAR(4096)
SET @msg = 'Failure occurred attempting to insert into tblXTABEmployee.';
RAISERROR(@msg, 0, 1);
END CATCH

CLOSE columnNameCursor
DEALLOCATE columnNameCursor
GO

Answer 3

Query your table using for xml and store the result in an XML variable. Then you query the XML variable to create the shape of XML you need using local-name(.) to get the column/node name from the XML variable..

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  FirstName varchar(25),
  LastName varchar(25),
  Etc int
)

insert into YourTable values ('John', 'Smith', 123)

Query 1:

declare @XML xml

set @XML = (
           select *
           from YourTable
           for xml path('row'), type
           )

select T.N.value('local-name(.)', 'sysname') as "@name",
       T.N.value('text()[1]', 'varchar(max)') as "value"
from @XML.nodes('/row/*') as T(N)
for xml path('field'), root('fields')

Results:

<fields>
  <field name="FirstName">
    <value>John</value>
  </field>
  <field name="LastName">
    <value>Smith</value>
  </field>
  <field name="Etc">
    <value>123</value>
  </field>
</fields>