Is there a MSSQL command to merge lots of rows data into a single row from a common key?

Question

I have a few tables filled with info like this:

jobDetails:
+-----------+------+---------+
| JOBNUMBER | DATA | KEY     |
+-----------+------+---------+
|  6015425  | .... | COLOUR  |
+-----------+------+---------+
|  6015425  | .... | SIZE    |
+-----------+------+---------+
|  6015425  | .... | WEIGHT  |
+-----------+------+---------+
|  6015425  | .... | ADDRESS |
+-----------+------+---------+

The .... string is the data i want.

Is there a MSSQL query i can use to return this information in one row? like this:

Jobnumber, Colour, Size, Weight, Address

because i have to process thousands of jobs and would like to return a single row for each.

Answer 1

use a join:

SELECT
    TableA.Col1, TableA.Col2, TableB.Col1
    FROM TableA
        INNER JOIN TableB ON TableA.Col_X=TableB.Col_Z

for your example:

SELECT
    a.JobNumber
        ,a.Data AS Colour
        ,b.Data AS Size
        ,c.Data AS Weight
        ,d.Data AS Address
    FROM JobDetails                 a
        LEFT OUTER JOIN JobDetails  b ON a.JobNumber=b.Jobnumber AND b.Key='SIZE'
        LEFT OUTER JOIN JobDetails  c ON a.JobNumber=c.Jobnumber AND c.Key='WEIGHT'
        LEFT OUTER JOIN JobDetails  d ON a.JobNumber=d.Jobnumber AND d.Key='ADDRESS'
    WHERE a.Key='COLOUR'

Answer 2

There's nothing fundamentally wrong with the design; it's EAV. This is quite a nice application of PIVOT. Since you're dealing with strings, use MIN or MAX as the aggregate that PIVOT requires.

SELECT  *
FROM    ( SELECT  *
          FROM    JobDetails) data PIVOT (MIN([DATA]) FOR [KEY] 
                                  IN ([COLOUR], [SIZE], [WEIGHT], [ADDRESS]) pvt

Answer 3

This is actually a very powerful schema design. It allows you to define new properties on entities in a very efficent way. This is similar to how dynamic languages define properties and methods.

With that said, SQL Server has limited support for this type of schema. If your properties are set at design time (i.e., each entity will only have the 4 properties), then you can use the PIVOT functionality. However, if the number of properties will change (which is the point of this type of schema), then you need a different solution. Your options are:

1. Use dynamic SQL with the PIVOT functionality
2. Do the transformation on the client
3. Use XML/XSLT on the server.

My personal perference is usually #3, unless the final application has been designed for #2.

Does this help?

Erick

Answer 4

PUT ALL UR COLUMNS ON A TABLE

AND THEN COMBINE THEM TO A CSV(COMMA SEP VARIABLE) USING

SELECT @COLUMNS = COALESCE(@COLUMNS +','+ CAST(HEADS AS VARCHAR) , CAST(HEADS AS VARCHAR)) FROM TABLE

THEN COUNT THE NUMBER OF ROWS PER EACH ID

LIKE 5 ROWS FOR 6015425

CREATE A TABLE WITH THAT COUNT LIKE

CREATE TABLE TEMP(ID INT)

SET @COUNT =(SELECT COUNT(*) FROM TABLE)

WHILE @COUNT >=0 BEGIN

ALTER TABKE ADD COLUMNSNAME END

THEN USE A DE COMMA SEPERATE TO PUT ATHE CSV INTO THE COLUMNS

LIKE

DECLARE @S VARCHAR(8000), @DATA VARCHAR(8000) --DROP TABLE #NORMALISEDTABLE --CREATE TABLE #NORMALISEDTABLE (HEADS NVARCHAR(200)) SELECT @S='' WHILE EXISTS (SELECT * FROM #HEADSCOMMA WHERE HEADS>@S) BEGIN SELECT @S=HEADS FROM #HEADSCOMMA WHERE HEADS>@S PRINT @S SELECT @DATA=''''+REPLACE(@S,',',''',''')+'''' PRINT @DATA INSERT INTO #TEMP EXEC('SELECT '+@DATA) END