Consolidate query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Consolidate query

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-26 : 07:03:08

hello there.

i have a table with multiple instances of the example below.

ref 1 2 3 4 5

140622 1 1 0 0 0
140622 0 0 0 1 0
140622 0 0 0 0 1
140623 0 0 1 0 0
140623 0 0 0 1 0
140623 1 1 0 0 0

how could i consolidate into on line per ref?

Regards

Rob

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-26 : 07:07:45

select max([1]),max([2]),max([3]),max([4]),max([5]) from table group by ref

Too old to Rock'n'Roll too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-26 : 07:36:57

thank you for your reply,
i have maxed all my number columns. but iget the below.

client_ref 1 2 3 4 5

1000 1 1 1 1 1
10000 1 1 1 1 1
100000 1 1 1 1 1
100001 1 1 1 1 1
100004 1 1 1 1 1
100005 1 1 1 1 1
100006 1 1 1 1 1
100009 1 1 1 1 1

i want to include 0's when there are some.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-26 : 07:39:32

for example

the below
140623 0 0 1 0 0
140623 0 0 0 1 0
140623 1 1 0 0 0

will become

140623 1 1 1 1 0

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-26 : 08:16:37

Once execute this
DECLARE @tab TABLE(ref INT, [1] INT, [2] INT, [3] INT, [4] INT, [5] INT)
INSERT INTO @tab
SELECT 140622, 1, 1, 0, 0, 0 UNION ALL
SELECT 140622, 0, 0, 0, 1, 0 UNION ALL
SELECT 140622, 0, 0, 0, 0, 1 UNION ALL
SELECT 140623, 0, 0, 1, 0, 0 UNION ALL
SELECT 140623, 0, 0, 0, 1, 0 UNION ALL
SELECT 140623, 1, 1, 0, 0, 0

select ref, max([1]),max([2]),max([3]),max([4]),max([5])
from @tab
group by ref

--
Chandu

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-26 : 08:23:39

isnt that just the same as

select max([1]),max([2]),max([3]),max([4]),max([5]) from table group by ref

i already have the data in a table

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-26 : 08:33:43

yes it is the same and it shows you that it works!

Too old to Rock'n'Roll too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-26 : 11:01:07