Microsoft SQL Server: Adding New Column To Row Instead Of Repeated Values

Question

Here's my current situation:

I'm presented with a long list of data, containing names and values, with a corresponding value ID. The number of IDs can be varying for data presented, but is constant for the whole set. (This set will have exactly 2 Values per name). The thing is, the names repeat, but the IDs do not. My question then becomes,

How can I convert a table that looks like this:

Name | ID | Value
Sam  |  1 |    15
Sam  |  2 |     6
Bob  |  1 |     9
Bob  |  2 |    11

Into something more like this:

Name | Value1 | Value2
Sam  |     15 |      6
Bob  |      9 |     11

Note: I'm finding a really hard time figuring out a title for this question.

Ionic · Accepted Answer · 2015-06-23T05:11:11.090

You can using pivot for this as example. Take a look at this code snippet:

SELECT pvt.Name, pvt.[1] as Value1, pvt.[2] as Value2
FROM yourTable
PIVTO (
    MAX(Value)
    FOR id IN([1],[2])
) as pvt

You'll just need to add your additional id's to the IN() and the SELECT.

If you need a dynamic pivot, you can use this one over here:

DECLARE @sql nvarchar(max), @columnlist nvarchar(max)

SELECT @columnlist = 
        COALESCE(@columnlist + N',['+CONVERT(nvarchar(max),cols.id)+']', 
            N'['+CONVERT(nvarchar(max),cols.id)+']'
        )
FROM (SELECT DISTINCT id FROM yourTable) as cols

-- this is your part
SET @sql = N'
    SELECT pvt.*
    FROM yourTable
    PIVTO (
        MAX(Value)
        FOR id IN('+@columnlist+')
    ) as pvt'
EXEC(@sql)

score 3 · Answer 2 · answered Jun 22 '15 at 21:00

3

If you can be sure that the set will only ever have two values and these will always have an ID of either 1 or 2, you could do something like the following:

CREATE TABLE #Table (Name VARCHAR(5), ID INT, Value INT)
INSERT INTO #Table VALUES ('Sam', 1, 15), ('Sam', 2, 6), ('Bob', 1, 9), ('Bob', 2, 11)

SELECT  t.Name,
        t.Value AS Value1,
        t2.Value AS Value2
  FROM  #Table t
    INNER JOIN #Table t2 ON t2.Name = t.Name AND t2.ID = 2
  WHERE t.ID = 1

DROP TABLE #Table

Note that the temporary table is just here to illustrate the result. The important thing is the SELECT statement.

This joins the table to itself to get the value for Value2.

answered Jun 22 '15 at 21:00

Martin

16,093
1
29
48

You'd need to rewrite this query to add/remove join operations depending on how many values you have associated with each name. I think that a pivot would be a more robust solution to the problem as a whole, although this solution would work well enough with the specific example provided. – DeadZone Jun 22 '15 at 21:06
I agree that `PIVOT` is a more suitable solution, but when there are only two values (as the OP indicated there would be) then this would perform more quickly. – Martin Jun 22 '15 at 21:07
1

Actually, the OP indicated that, "The number of IDs can be varying for data presented" but that this example, "will have exactly 2 Values per name." – DeadZone Jun 22 '15 at 21:08
@DeadZone In which case `PIVOT` will not solve the problem either, as it will pivot each separate ID into it's own column. – Martin Jun 22 '15 at 21:11
I'm confused... Isn't that exactly what the OP wants? Each ID becomes it's own column with the values being listed within that column. And that is exactly what your query does as well, except that it is hard-coded to values 1 and 2. – DeadZone Jun 22 '15 at 21:16
@DeadZone I was under the impression that OP wants the two values displayed in the two same columns: `This set will have exactly 2 Values per name`. Perhaps we should ask the OP for clarification rather than discussing it inside my answer – Martin Jun 22 '15 at 21:19
@MartinParkin Although this answer does work for the specific case where there are only 2 values per person, however one would need to add/remove code to get to the correct number of values if let's say it was 3 values instead of only 2. I was looking for a more general solution, but this answer helped me out a little bit. Thanks – SamuelSVD Jun 23 '15 at 15:59

score 3 · Answer 3 · edited May 23 '17 at 11:57

What you need is a PIVOT query. Depending on how many Names you have in your data set, and whether or not you know them ahead of time, you will probably need a "dynamic Pivot".

Pivot queries can be a little confusing. But here are two examples of how to build a dynamic pivot query. (The 2nd one is another Stack Overflow question, and might be better for your situation than the first one.)

T-SQL: Dynamic Pivot on Multiple Columns

T-SQL dynamic pivot

EDIT: Specific example...

-- DROP TABLE #MyTable
-- CREATE TABLE #MyTable( name varchar(10), ID int, value int );

INSERT #MyTable VALUES ('Sam', 1, 15);
INSERT #MyTable VALUES ('Sam', 2, 6);
INSERT #MyTable VALUES ('Bob', 1, 9);
INSERT #MyTable VALUES ('Bob', 2, 11);
-- INSERT #MyTable VALUES ('Sam', 3, 1);
-- INSERT #MyTable VALUES ('Bob', 3, 2);


DECLARE @cols NVARCHAR(2000) 
DECLARE @query NVARCHAR(4000) 

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT 
                                '],[' + CONVERT(varchar(5), t.ID )
                        FROM    #MyTable AS t 
                        --ORDER BY '],[' + t.ID 
                        FOR XML PATH('') 
                      ), 1, 2, '') + ']' 

--SELECT  @cols

SET @query = N'SELECT name,'+ @cols +' FROM 
(SELECT t1.name, t1.ID, t1.Value FROM #MyTable AS t1) p 
PIVOT (MAX([Value]) FOR ID IN ( '+ @cols +' )) 
AS pvt;' 

EXECUTE(@query)

This seems to do exactly what it was I was looking for. I attempted using this bit of code with 3 and 4 values per name, and it worked wonderfully. Thanks @DeadZone — SamuelSVD, Jun 23 '15 at 16:01

Microsoft SQL Server: Adding New Column To Row Instead Of Repeated Values

3 Answers3

Linked