July 19, 2017 at 1:33 pm
Hey guys Suppose I had the following table columns as well as the row:
MyTable
Col_A Col_B Col_C Col_D
Y N Y N
How could I select the column names which have the value 'N' into 1 column? The resultset would be
Returned_Column
Col_B Col_D
July 19, 2017 at 1:51 pm
Can you post DDL, create table and inserts with sample data?
July 19, 2017 at 1:51 pm
You've been around long enough to know how to post sample data and expected results. Also, one row isn't sufficient. Adding even one more row to the data creates an ambiguous situation.
SELECT *
FROM ( VALUES
('Y', 'N', 'Y', 'N'),
('Y', 'N', 'N', 'N')
)v(Col_A, Col_B, Col_C, Col_D)
/* I don't think you really want these results,
but there's no way to tell from your sample data. */
SELECT *
FROM ( VALUES
('Col_B Col_C Col_D')
)v(returned_columns)
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 19, 2017 at 2:07 pm
Sorry guys, it reformatted the layout that I was seeing when posting it.
Table
Create Table dbo.MyTable (
ID int Identity(1,1) NOT NULL
,Col_A varchar(1) NOT NULL
,Col_B varchar(1) NOT NULL
,Col_C varchar(1) NOT NULL
,Col_D varchar(1) NOT NULL
)
Insert into dbo.MyTable (Col_A, Col_B, Col_C, Col_D)
Values ('Y', 'N', 'Y', 'N')
, ('N','Y','Y','N')
,('Y','N','N','N')
I would want to select the all rows including the ID and another column with the column names which contain the value 'N'
Select ID, (all column names for this row that contain 'N') as N_Containing_Columns_For_Row
Result Set
1, Col_B Col_D
2, Col_A Col_D
3, Col_B Col_C Col_D
July 19, 2017 at 2:12 pm
DROP TABLE IF EXISTS #DodgyData;
CREATE TABLE #DodgyData
(
Id INT IDENTITY(1, 1)
, Col_A CHAR(1)
, Col_B CHAR(1)
, Col_C CHAR(1)
, Col_D CHAR(1)
);
INSERT #DodgyData
(
Col_A
, Col_B
, Col_C
, Col_D
)
VALUES
(
'Y', 'N', 'Y', 'N'
)
,(
'N', 'Y', 'Y', 'N'
)
,(
'Y', 'N', 'N', 'N'
);
SELECT
dd.Id
, CONCAT(IIF(dd.Col_A = 'N', 'Col_A ', ''), IIF(dd.Col_B = 'N', 'Col_B ', ''), IIF(dd.Col_C = 'N', 'Col_C ', ''), IIF(dd.Col_D = 'N', 'Col_D ', ''))
FROM #DodgyData dd;
--Edit: Sorry, need to change the DROP IF EXISTS to work in 2012.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply