CODEWITHSUNDEEP
sqlsql-server
Astroc
Astroc

Reputation: 33

Selecting multiple values into single row - SQL server

I need to merge a table with ID and various bit flags like this 

-----------------
a1 | x |   | x |
-----------------
a1 |   | x |   |
-----------------
a1 |   |   |   |
-----------------
b2 | x |   |   |
-----------------
b2 |   |   |   |
-----------------
c3 | x | x | x |

into such form

-----------------
a1 | x | x | x |
-----------------
b2 | x |   |   |
-----------------
c3 | x | x | x |

The problem is that data are join by kind of option ID each option has an unique ID which is joined with a1, b2. When I try to SELECT it by using DISTINCT I receive results from table number 1. I can make it by subqueries in SELECT but it is really weak solution due to performance reasons.

Do you have any idea how select and combine all these flags into single row?

Upvotes: 1

Views: 594

Answers (4)

StepUp
StepUp

Reputation: 38104

For the given result set it is eligible to use MIN and GROUP BY:

SELECT 
tbl.Col
, MIN(tbl.Col1) Col1
, MIN(tbl.Col2) Col2 
, MIN(tbl.Col3) Col3 
FROM @table tbl
GROUP BY tbl.Col

However, if you have empty rows, then use MAX(). Otherwise MIN() returns empty rows:

SELECT 
tbl.Col
, MAX(tbl.Col1) Col1
, MAX(tbl.Col2) Col2 
, MAX(tbl.Col3) Col3 
FROM @table tbl
GROUP BY tbl.Col

For example: 

DECLARE @table TABLE
(
    Col  VARCHAR(50),
    Col1 VARCHAR(50), 
    Col2 VARCHAR(50), 
    Col3 VARCHAR(50) 
)

INSERT INTO @table
(
    Col,
    Col1,
    Col2,
    Col3
)
VALUES
(   'a1', -- Col - varchar(50)
    'x', -- Col1 - varchar(50)
    Null, -- Col2 - varchar(50)
    'x'  -- Col3 - varchar(50)
    )
, ('a1', NULL, 'x', null)
, ('a1', NULL, 'x', null)
, ('b2', 'x', null, null)
, ('b2', null, null, null)
, ('c3', 'x', 'x', 'x')

SELECT 
tbl.Col
, MIN(tbl.Col1) Col1
, MIN(tbl.Col2) Col2 
, MIN(tbl.Col3) Col3 
FROM @table tbl
GROUP BY tbl.Col

OUTPUT:

Col Col1    Col2    Col3
a1   x        x       x
b2   x       NULL   NULL
c3   x        x       x

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

The general solution for such a situation is to simply aggregate and either use MIN or MAX on the columns.

SQL Server's data type BIT, however, is quirky. It's a little like a BOOLEAN, but not a real boolean. It is a little like a very limited numeric data type, but it isn't really a numeric type either. And there simply exist no aggregation functions for this data type. In standard SQL you'd have ANY and EVERY for the BOOLEAN type. In PostgreSQL you have BIT_OR and BIT_AND for BIT and BOOL_OR and BOOL_AND for BOOLEAN. SQL Server has nothing.

So convert your columns to a numeric type before using MIN (which would be a bitwise AND) or MAX (which would be a bitwise OR) on it. E.g.

select
  id,
  max(bit1 + 0) as bit1agg,
  max(bit2 + 0) as bit2agg,
  max(bit3 + 0) as bit3agg
from mytable
group by id
order by id;

You can also use CAST or CONVERT instead of course.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want aggregation :

select col1, max(col2), max(col2), max(col3)
from table t
group by col1;

This assuming blank value as null.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use aggregation

select col1 ,max(col2),max(col3),max(col4)
form table_name group by col1

Upvotes: 3

Related Questions