CODEWITHSUNDEEP
sqlsql-server
SamuelSVD
SamuelSVD

Reputation: 112

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

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.

Upvotes: 0

Views: 1994

Answers (3)

Ionic
Ionic

Reputation: 3935

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)

Upvotes: 5

DeadZone
DeadZone

Reputation: 1680

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)

Upvotes: 3

Martin
Martin

Reputation: 16423

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.

Upvotes: 3

Related Questions