fes fes - 6 months ago 10
SQL Question

SQL multiple rows with same row names to column

I have the following data in SQL (nvarchar, nvarchar)

Name: Test Person
Phone Number: 290831283
Fax Number: 192389182
Email Address: test@test.com
Name: Abacus Testing
Phone Number: 901823908
Fax Number: 9213989182
Email Address: abacus@test.com


How can I format this data to be:

[Name] [Phone Number] [Fax Number] [Email Address]
Test Person 290831283 192389182 test@test.com
Abacus Testing 901823908 9213989182 abacus@test.com


So basically setting the rows into matching column

I was hoping to use a Pivot table, but I only get the first row due to aggregation.

SELECT [Name], [Phone Number], [Fax Number], [Email Address]
FROM
(
SELECT
ColumnName,
Data
FROM
TheData
) SRC
PIVOT
(
MAX(Data)
FOR ColumnName IN ([Name], [Phone Number], [Fax Number], [Email Address])
) PIV


I would prefer no CURSOR methods, any alternatives?


  • Edit: added other fields available for use



The other fields available are Label Id for each entry. i.e Name=1, PhoneNumber=2, FaxNumber=3, EmailAddress=4

The common Id for each of the 4 entries is also available. i.e first 4 entries have id 1001, second 4 entries have id 1002

Answer

Following clarification in the comments you just need to add CommonId in to your source.

This is not an aggregated or spreading column so it will be used as a grouping column and you will get a row per distinct value of that.

SELECT [Name], [Phone Number], [Fax Number], [Email Address]
FROM
(
    SELECT 
            CommonId,
            ColumnName,
            Data
    FROM 
            TheData
) SRC
PIVOT
(
  MAX(Data)
  FOR ColumnName IN ([Name], [Phone Number], [Fax Number], [Email Address])
) PIV
Comments