| Author |
 Topic  |
|
|
masterdineen
Aged Yak Warrior
United Kingdom
522 Posts |
 Posted - 02/26/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 02/26/2013 : 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
United Kingdom
522 Posts |
Posted - 02/26/2013 : 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
United Kingdom
522 Posts |
Posted - 02/26/2013 : 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
Flowing Fount of Yak Knowledge
India
1715 Posts |
Posted - 02/26/2013 : 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
United Kingdom
522 Posts |
Posted - 02/26/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 02/26/2013 : 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
United Kingdom
522 Posts |
Posted - 02/26/2013 : 11:01:07
|
| My data is aready in a table, so unable to us union all, i want to be able to query directly to table |
|
|
|
masterdineen
Aged Yak Warrior
United Kingdom
522 Posts |
Posted - 02/26/2013 : 11:48:21
|
| sorted it, thank you for your help |
|
|
| |
Topic |
|
|
|
Consolidate query
