Selecting multiple values into single row - SQL server

Question

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?

Simply GROUP BY.

jarlh
– jarlh

2019-02-20 13:15:51 +00:00
Commented Feb 20, 2019 at 13:15 — jarlh
– jarlh, Commented Feb 20, 2019 at 13:15

Zaynul Abadin Tuhin · Accepted Answer · 2019-02-20 13:16:06Z

3

use aggregation

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

answered Feb 20, 2019 at 13:16

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StepUp · Accepted Answer · 2019-02-20 15:18:43Z

1

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

edited Feb 20, 2019 at 15:18

answered Feb 20, 2019 at 13:16

StepUp

38.5k16 gold badges95 silver badges159 bronze badges

3 Comments

StepUp Over a year ago

@Larnu Yeah, for this result set it works correctly.

Thorsten Kettner Over a year ago

I don't understand. MIN skips nulls and MAX doesn't? That's new to me.

Thorsten Kettner Over a year ago

MIN and MAX both ignore nulls So both return the non-null value. Only if two or more non-null values per ID exist, would MIN return the lesser value and MAX the greater one. This has nothing to do with "empty rows".

Yogesh Sharma · Accepted Answer · 2019-02-20 13:16:17Z

1

You want aggregation :

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

This assuming blank value as null.

answered Feb 20, 2019 at 13:16

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

Thorsten Kettner · Accepted Answer · 2019-02-20 13:51:07Z

1

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.

answered Feb 20, 2019 at 13:51

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

1 Comment

Astroc Over a year ago

Yep I casted Bit as integer and it works fine :) Thank you for reply

Collectives™ on Stack Overflow

Selecting multiple values into single row - SQL server

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related