August 22, 2014 at 9:50 am
From following sample data, I like to select fields that yearmonth [201401] thru [201406] =1 and remove the rest of the records. Desired outcome is below the codes
Current Data:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
ID INT,
Name char(20) ,
[201401] int,
[201402] int,
[201403] int,
[201404] int,
[201405] int,
[201406] int
)
--===== Insert the test data into the test table
INSERT INTO #Table1
(ID, Name, [201401],[201402],[201403],[201404],[201405],[201406])
SELECT 1,Joe, 0 , 0, 0, 0, 1, 0 UNION ALL
SELECT 1,Joe, 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 2,Jill, 1 , 0, 0, 0, 0, 0 UNION ALL
SELECT 3,Scott, 0 , 0, 1, 0, 0, 0 UNION ALL
SELECT 3,Scott, 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 4,Sandy, 0 , 0, 0, 0, 0, 1
select *
from #Table1
Desired Outcome:
IDName201401201403201405201406
1Joe 0010
2Jill 1000
3Scott 0100
4Sandy 0001
Thank You,
Helal
August 22, 2014 at 10:52 am
select *
from #Table1
where [201401]+[201402]+[201403]+[201404]+[201405]+[201406] <> 0
August 22, 2014 at 10:53 am
One, please make sure to test your code in an empty database prior to posting.
Two, the following works:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
ID INT,
Name char(20) ,
[201401] int,
[201402] int,
[201403] int,
[201404] int,
[201405] int,
[201406] int
)
--===== Insert the test data into the test table
INSERT INTO #Table1
(ID, Name, [201401],[201402],[201403],[201404],[201405],[201406])
SELECT 1,'Joe', 0 , 0, 0, 0, 1, 0 UNION ALL
SELECT 1,'Joe', 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 2,'Jill', 1 , 0, 0, 0, 0, 0 UNION ALL
SELECT 3,'Scott', 0 , 0, 1, 0, 0, 0 UNION ALL
SELECT 3,'Scott', 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 4,'Sandy', 0 , 0, 0, 0, 0, 1
select *
from #Table1
where
[201401] = 1 or
[201402] = 1 or
[201403] = 1 or
[201404] = 1 or
[201405] = 1 or
[201406] = 1;
August 22, 2014 at 11:08 am
Lynn Pettis (8/22/2014)
One, please make sure to test your code in an empty database prior to posting.Two, the following works:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
ID INT,
Name char(20) ,
[201401] int,
[201402] int,
[201403] int,
[201404] int,
[201405] int,
[201406] int
)
--===== Insert the test data into the test table
INSERT INTO #Table1
(ID, Name, [201401],[201402],[201403],[201404],[201405],[201406])
SELECT 1,'Joe', 0 , 0, 0, 0, 1, 0 UNION ALL
SELECT 1,'Joe', 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 2,'Jill', 1 , 0, 0, 0, 0, 0 UNION ALL
SELECT 3,'Scott', 0 , 0, 1, 0, 0, 0 UNION ALL
SELECT 3,'Scott', 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 4,'Sandy', 0 , 0, 0, 0, 0, 1
select *
from #Table1
where
[201401] = 1 or
[201402] = 1 or
[201403] = 1 or
[201404] = 1 or
[201405] = 1 or
[201406] = 1;
Lynn,
you are missin a point here
The op doesn't want all the columns
ID Name 201401 201403 201405 201406
i believe he wants a column only when the sum is >= 1
in the output he is not including the columns 201402 and 201404
thanks
August 22, 2014 at 11:12 am
rxm119528 (8/22/2014)
Lynn Pettis (8/22/2014)
One, please make sure to test your code in an empty database prior to posting.Two, the following works:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
ID INT,
Name char(20) ,
[201401] int,
[201402] int,
[201403] int,
[201404] int,
[201405] int,
[201406] int
)
--===== Insert the test data into the test table
INSERT INTO #Table1
(ID, Name, [201401],[201402],[201403],[201404],[201405],[201406])
SELECT 1,'Joe', 0 , 0, 0, 0, 1, 0 UNION ALL
SELECT 1,'Joe', 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 2,'Jill', 1 , 0, 0, 0, 0, 0 UNION ALL
SELECT 3,'Scott', 0 , 0, 1, 0, 0, 0 UNION ALL
SELECT 3,'Scott', 0 , 0, 0, 0, 0, 0 UNION ALL
SELECT 4,'Sandy', 0 , 0, 0, 0, 0, 1
select *
from #Table1
where
[201401] = 1 or
[201402] = 1 or
[201403] = 1 or
[201404] = 1 or
[201405] = 1 or
[201406] = 1;
Lynn,
you are missin a point here
The op doesn't want all the columns
ID Name 201401 201403 201405 201406
i believe he wants a column only when the sum is >= 1
in the output he is not including the columns 201402 and 201404
thanks
Yep, your right. Must have been the IDF's we received and I missed all of the expected results.
Well, looks like some dynamic SQL is needed and I have other pressing things to work on at the moment. Maybe later tonight I can come back to this.
August 22, 2014 at 11:21 am
Looks like a combination of upivot and pivot may work. Just don't have time to play with it at the moment.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply