Is SQL powerful enough to do this?

Our legacy app exports data into an SQL table in the following format

**Row#** | **Column#** | **Parameter** | **Value**

e.g.

1 | 1 | Name  | Michael

1 | 2 | Age   | 30

1 | 3 | email | email@email.com

2 | 1 | Name  | Brian

2 | 2 | Age   | 45

2 | 3 | e开发者_StackOverflow中文版mail | brian@email.com

And I need to, using only SQL, return something like this

**Name** | **Age** | **email**

Michael  |    30   | email@email.com

Brian    |    45   | brian@email.com

Is this possible? If so, how? Further details:

• The number of columns and the column names ARE fixed and could be hard coded

• The number of rows is not fixed

You could use PIVOT, or just some fun with aggregates:

SELECT
    Row#,
    MAX(CASE WHEN Parameter='Name' THEN Value END) as Name,
    MAX(CASE WHEN Parameter='Age' THEN Value END) as Age,
    MAX(CASE WHEN Parameter='email' THEN Value END) as Email
FROM
    Table
group by
    Row#

Exact syntax, and which option to use, will depend on what SQL database you're using

You need a PIVOT or CROSS TAB query. Your RDBMS may have specific support for this. If not the following should work pretty much anywhere AFAIK.

SELECT
       MAX(CASE WHEN Column=1 then Value END) AS Name,
       MAX(CASE WHEN Column=2 then Value END) AS Age,
       MAX(CASE WHEN Column=3 then Value END) AS Email
FROM YourTable
GROUP BY row

SELECT
        A.Name,
        B.Age,
        C.email
FROM
        myTable A
        LEFT OUTER JOIN myTable B
            ON A.Row = B.Row
                AND B.Parameter = 'Age'
        LEFT OUTER JOIN myTable C
            ON A.Row = C.Row
                AND C.Parameter = 'email'
WHERE
        A.Parameter = 'Name'

Continue with more table aliases as needed. Note that if your table is very wide, this can be arduous to maintain.

So your data is in Entity-Attribute-Value format. Thats not uncommon.

You can transfer it to tabular format with a series of joins, or a pivot operation.

See other answers for the SQL join methods

If you're using SQL Server, the SQL Server PIVOT command to do it would look something like this:

    SELECT Row
    , [Name] 
    , [age] 
    , [email] 
    FROM 
    (
        SELECT [Row], [Parameter], [Value]
        FROM EAV_Data
    ) p
    PIVOT
    (
        MAX ([Value])
        FOR Parameter IN ([Name], [age], [email])
    ) AS pvt