CODEWITHSUNDEEP
sqlsql-server
Meto
Meto

Reputation: 440

SQL Server, Merge two records in one record

We have these tables

CREATE TABLE tbl01
(
    [id] int NOT NULL PRIMARY KEY,
    [name] nvarchar(50) NOT NULL
)

CREATE TABLE tbl02
(
    [subId] int NOT NULL PRIMARY KEY ,
    [id] int NOT NULL REFERENCES tbl01(id),
    [val] nvarchar(50) NULL,
    [code] int NULL
)

If we run this query:

SELECT  
   tbl01.id, tbl01.name, tbl02.val, tbl02.code
FROM    
   tbl01 
INNER JOIN 
   tbl02 ON tbl01.id = tbl02.id

we get these results:

-------------------------------
id  | name  | val       | code
-------------------------------
1   | one   | FirstVal  | 1
1   | one   | SecondVal | 2
2   | two   | YourVal   | 1
2   | two   | OurVal    | 2
3   | three | NotVal    | 1
3   | three | ThisVal   | 2
-------------------------------

You can see that each two rows are related to same "id"

The question is: we need for each id to retrieve one record with all val, each val will return in column according to the value of column code

if(code = 1) then val as val-1
else if (code = 2) then val as val-2

Like this:

-------------------------------
id  | name  | val-1     | val-2
-------------------------------
1   | one   | FirstVal  | SecondVal
2   | two   | YourVal   | OurVal
3   | three | NotVal    | ThisVal
-------------------------------

Any advice?

Upvotes: 2

Views: 10905

Answers (5)

mvisser
mvisser

Reputation: 670

Try this - it uses a pivot function but it also creates creates the dynamic columns dependent on code 

 DECLARE @ColumnString varchar(200)
 DECLARE @sql varchar(1000)


CREATE TABLE #ColumnValue
(
  Value varchar(500)
)

INSERT INTO #ColumnValue (Value)
SELECT DISTINCT '[' + 'value' + Convert(Varchar(20),ROW_NUMBER() Over(Partition by id Order by id )) + ']'
FROM Test

SELECT @ColumnString = COALESCE(@ColumnString + ',', '') + Value
FROM #ColumnValue
Drop table #ColumnValue



SET @sql =
'
SELECT *
FROM
(
SELECT
        id,name,val,''value'' + Convert(Varchar(20),ROW_NUMBER() Over(Partition by id Order by id ))as [values]
FROM Test
) AS P
    PIVOT
(
    MAX(val) FOR [values] IN ('+@ColumnString+')
) AS pv
'

--print @sql 
EXEC (@sql)

Upvotes: 0

Joel Harkes
Joel Harkes

Reputation: 11661

If there are always only two values, you could join them or even easier, group them:

SELECT  tbl01.id as id, Min(tbl01.name) as name, MIN(tbl02.val) as val-1, MAX(tbl02.val) as val-2
FROM    tbl01 
INNER JOIN tbl02 ON tbl01.id = tbl02.id
GROUP BY tbl02.id

note: this query will always put the lowest value in the first column and highest in the second, if this is not wanted: use the join query:

Join query

If you always want code 1 in the first column and code 2 in the second:

SELECT  tbl01.id as id, tbl01.name as name, tbl02.val as val-1, tbl03.val as val-2
    FROM    tbl01 
    INNER JOIN tbl02 ON tbl01.id = tbl02.id
    ON tbl02.code = 1
    INNER JOIN tbl03 ON tbl01.id = tbl03.id
    ON tbl03.code = 2

Variable amount of columns

You cannot get an variable amount of columns, only when you do this by building your query in code or t-sql stored procedures.

My advice: If its always to values: join them in query, if not, let your server-side code transform the data. (or even better, find a way which makes it not nessecery to transform data)

Upvotes: 1

James S
James S

Reputation: 3588

You've already got a few answers, but heres one using PIVOT as an alternative. The good thing is this approach is easy to scale if there are additional columns required later

-- SETUP TABLES
DECLARE @t1 TABLE (
    [id] int NOT NULL PRIMARY KEY,
    [name] nvarchar(50) NOT NULL
)
DECLARE @t2 TABLE(
    [subId] int NOT NULL PRIMARY KEY ,
    [id] int NOT NULL,
    [val] nvarchar(50) NULL,
    [code] int NULL
)

-- SAMPLE DATA
INSERT @t1  ( id, name )
VALUES  ( 1, 'one'), (2, 'two'), (3, 'three')
INSERT @t2
        ( subId, id, val, code )
VALUES  ( 1,1,'FirstVal', 1), ( 2,1,'SecondVal', 2)
       ,( 3,2,'YourVal', 1), ( 4,2,'OurVal', 2)
       ,( 5,3,'NotVal', 1), ( 6,3,'ThisVal', 2)


-- SELECT (using PIVOT)

SELECT id, name, [1] AS 'val-1', [2] AS 'val-2' 
FROM
( 
SELECT t2.id, t1.name, t2.val, t2.code 
FROM @t1 AS t1 JOIN  @t2 AS t2 ON t2.id = t1.id
) AS src
PIVOT
(
    MIN(val)
    FOR code IN ([1], [2])
) AS pvt

results:

id  name    val-1       val-2
---------------------------------
1   one     FirstVal    SecondVal
2   two     YourVal     OurVal
3   three   NotVal      ThisVal

Upvotes: 1

AA.SC
AA.SC

Reputation: 377

Use can use MAX and Group By to achieve this 

SELECT  id,
        name,
        MAX([val1]) [val-1],
        MAX([val2]) [val-2]
FROM    ( SELECT    tbl01.id, tbl01.name,
                    CASE code
                      WHEN 1 THEN tbl02.val
                      ELSE ''
                    END [val1],
                    CASE code
                      WHEN 2 THEN tbl02.val
                      ELSE ''
                    END [val2]
          FROM      tbl01
                    INNER JOIN tbl02 ON tbl01.id = tbl02.id
        ) Tbl
GROUP BY id, name

Upvotes: 3

HansLindgren
HansLindgren

Reputation: 369

Is it the PIVOT operator (http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) that you are looking for?

Upvotes: 1

Related Questions