SQLTeam.com | Weblogs | Forums

SQL Query to return column name when value is 1

Hi,

I'm not sure if this is possible to obtain hence my request for help. Basically I need to know if it's possible to run an SQL query which returns the column name where the value stored is 1.

Attached are an example of the table and its content and the result I need to obtain from the query if it's always possible.

Table_QueryResult

Thank you for your help.

Your database is not normalised. SQL is very suitable to process normalised databases. Processing a denormalised database is far far harder.

Step one: normalise the table:

DROP TABLE IF EXISTS dbo.DaTable;

CREATE TABLE dbo.DaTable(
	CUSTSYSREF	int NOT NULL	PRIMARY KEY,
	REPSSYSREF	int	NOT NULL,
	MON	bit	NOT NULL	DEFAULT 0,
	TUE	bit	NOT NULL	DEFAULT 0,
	WED	bit	NOT NULL	DEFAULT 0,
	THU	bit	NOT NULL	DEFAULT 0,
	FRI	bit	NOT NULL	DEFAULT 0,
	SAT	bit	NOT NULL	DEFAULT 0,
	SUN	bit	NOT NULL	DEFAULT 0
)

INSERT INTO dbo.DaTable(CUSTSYSREF, REPSSYSREF, MON, TUE, WED, THU, FRI, SAT, SUN) VALUES
(231, 15, 0, 0, 1, 0, 0, 0, 0),
(232, 15, 0, 1, 0, 0, 0, 0, 0),
(233, 16, 1, 0, 1, 0, 0, 0, 0),
(234, 17, 0, 0, 1, 0, 0, 1, 0)

SELECT * FROM dbo.DaTable

DROP TABLE IF EXISTS dbo.ParentTable;
DROP TABLE IF EXISTS dbo.ChildTable;
DROP TABLE IF EXISTS dbo.DaDaysTable;

CREATE TABLE dbo.DaDaysTable(
	Day_Of_Week_id	int	NOT NULL PRIMARY KEY,
	Day_Of_Week	CHAR(3)	
)
INSERT INTO dbo.DaDaysTable(Day_Of_Week_id, Day_Of_Week) VALUES 
(1, 'MON'), (2, 'TUE'), (3, 'WED'), (4, 'THU'), (5, 'FRI'), (6, 'SAT'), (7, 'SUN');

CREATE TABLE dbo.ParentTable(
	parent_id	int	IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	CUSTSYSREF	int NOT NULL,
	REPSSYSREF	int	NOT NULL,
)

INSERT INTO dbo.ParentTable(CUSTSYSREF, REPSSYSREF)
SELECT DISTINCT CUSTSYSREF, REPSSYSREF
FROM dbo.DaTable
GO
SELECT * from dbo.ParentTable

CREATE TABLE dbo.ChildTable(
	id	int	IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	parent_id	int	NOT NULL,
	Day_Of_Week_id	int NOT NULL
)

INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
	, DOW.day_of_week_id
FROM dbo.DaTable as DT
	INNER JOIN dbo.ParentTable as PT
		ON DT.CUSTSYSREF = PT.CUSTSYSREF
		AND DT.REPSSYSREF = PT.REPSSYSREF
	INNER JOIN dbo.DaDaysTable as DOW
		ON DOW.Day_Of_Week = 'MON'
			AND DT.MON = 1

INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
	, DOW.day_of_week_id
FROM dbo.DaTable as DT
	INNER JOIN dbo.ParentTable as PT
		ON DT.CUSTSYSREF = PT.CUSTSYSREF
		AND DT.REPSSYSREF = PT.REPSSYSREF
	INNER JOIN dbo.DaDaysTable as DOW
		ON DOW.Day_Of_Week = 'TUE'
			AND DT.TUE = 1

INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
	, DOW.day_of_week_id
FROM dbo.DaTable as DT
	INNER JOIN dbo.ParentTable as PT
		ON DT.CUSTSYSREF = PT.CUSTSYSREF
		AND DT.REPSSYSREF = PT.REPSSYSREF
	INNER JOIN dbo.DaDaysTable as DOW
		ON DOW.Day_Of_Week = 'WED'
			AND DT.WED = 1

INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
	, DOW.day_of_week_id
FROM dbo.DaTable as DT
	INNER JOIN dbo.ParentTable as PT
		ON DT.CUSTSYSREF = PT.CUSTSYSREF
		AND DT.REPSSYSREF = PT.REPSSYSREF
	INNER JOIN dbo.DaDaysTable as DOW
		ON DOW.Day_Of_Week = 'THU'
			AND DT.THU = 1

INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
	, DOW.day_of_week_id
FROM dbo.DaTable as DT
	INNER JOIN dbo.ParentTable as PT
		ON DT.CUSTSYSREF = PT.CUSTSYSREF
		AND DT.REPSSYSREF = PT.REPSSYSREF
	INNER JOIN dbo.DaDaysTable as DOW
		ON DOW.Day_Of_Week = 'FRI'
			AND DT.FRI = 1

INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
	, DOW.day_of_week_id
FROM dbo.DaTable as DT
	INNER JOIN dbo.ParentTable as PT
		ON DT.CUSTSYSREF = PT.CUSTSYSREF
		AND DT.REPSSYSREF = PT.REPSSYSREF
	INNER JOIN dbo.DaDaysTable as DOW
		ON DOW.Day_Of_Week = 'SAT'
			AND DT.SAT = 1

INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
	, DOW.day_of_week_id
FROM dbo.DaTable as DT
	INNER JOIN dbo.ParentTable as PT
		ON DT.CUSTSYSREF = PT.CUSTSYSREF
		AND DT.REPSSYSREF = PT.REPSSYSREF
	INNER JOIN dbo.DaDaysTable as DOW
		ON DOW.Day_Of_Week = 'SUN'
			AND DT.SUN = 1

Step two: get the result you want, based on a normalised table:

;WITH CTE AS(
SELECT CT.parent_id
	, DOW.day_of_week
	, ROW_NUMBER() OVER (PARTITION BY CT.parent_id ORDER BY DOW.Day_Of_Week_id ASC) as RowNum
FROM dbo.ChildTable as CT
	INNER JOIN dbo.DaDaysTable as DOW
		ON CT.Day_Of_Week_id = DOW.Day_Of_Week_id
)
SELECT PT.CUSTSYSREF
	, PT.REPSSYSREF
	, COALESCE(FirstDay.Day_Of_Week, '') as DayOfCall1
	, COALESCE(SecondDay.Day_Of_Week, '') as DayOfCall2
FROM dbo.ParentTable as PT
	LEFT OUTER JOIN CTE as FirstDay
		ON PT.parent_id = FirstDay.parent_id
		AND FirstDay.RowNum = 1
	LEFT OUTER JOIN CTE as SecondDay
		ON PT.parent_id = SecondDay.parent_id
		AND SecondDay.RowNum = 2

Hi

i had to re arrange the data using SQL ...
before getting the answer

please click arrow to the left for Drop Create DATA
DROP TABLE dbo.DaTable;

CREATE TABLE dbo.DaTable(
	CUSTSYSREF	int NOT NULL	PRIMARY KEY,
	REPSSYSREF	int	NOT NULL,
	MON	bit	NOT NULL	DEFAULT 0,
	TUE	bit	NOT NULL	DEFAULT 0,
	WED	bit	NOT NULL	DEFAULT 0,
	THU	bit	NOT NULL	DEFAULT 0,
	FRI	bit	NOT NULL	DEFAULT 0,
	SAT	bit	NOT NULL	DEFAULT 0,
	SUN	bit	NOT NULL	DEFAULT 0
)

INSERT INTO dbo.DaTable(CUSTSYSREF, REPSSYSREF, MON, TUE, WED, THU, FRI, SAT, SUN) VALUES
(231, 15, 0, 0, 1, 0, 0, 0, 0),
(232, 15, 0, 1, 0, 0, 0, 0, 0),
(233, 16, 1, 0, 1, 0, 0, 0, 0),
(234, 17, 0, 0, 1, 0, 0, 1, 0)

SELECT 'table data ', * FROM dbo.DaTable

image

please click arrow to the left for SQL ...
; with cte_weekday as
(
	select 'MON' as weekday ,1  as OrderBy
		union all 
	select 'TUE',2
		union all 
	select 'WED',3 
		union all 
	select 'THU',4
		union all 
	select 'FRI',5
		union all 
	select 'SAT',6
		union all 
	select 'SUN',7
) ,cte_daycall as 
(
	select CUSTSYSREF, REPSSYSREF,mon  as Weekday,'MON' as day from dbo.DaTable
		union all 
	select CUSTSYSREF, REPSSYSREF,tue		     ,'TUE'        from dbo.DaTable
		union all
	select CUSTSYSREF, REPSSYSREF,wed            ,'WED'        from dbo.DaTable
		union all 
	select CUSTSYSREF, REPSSYSREF,thu            ,'THU'        from dbo.DaTable
		union all 
	select CUSTSYSREF, REPSSYSREF,FRI            ,'FRI'        from dbo.DaTable
		union all
	select CUSTSYSREF, REPSSYSREF,SAT            ,'SAT'        from dbo.DaTable
		union all 
	select CUSTSYSREF, REPSSYSREF,SUN            ,'SUN'        from dbo.DaTable
) , cte_rn as 
(
select 
    a.* 
  , b.OrderBy
  , ROW_NUMBER() over (partition by CUSTSYSREF,REPSSYSREF order by orderby  ) as rn
from 
    cte_daycall  a 
	  join cte_weekday b 
	    on 
		 a.day = b.weekday
where 
    a.Weekday = 1 
), cte_callday as 
(
select 
   CUSTSYSREF
  ,REPSSYSREF
  ,case when rn = 1 then Day end  as CallDay1  
  ,case when rn = 2 then Day  end as CallDay2  
from 
   cte_rn 
)
select 
    CUSTSYSREF
  , REPSSYSREF
  , max(CallDay1)
  , max(CallDay2)
  from 
   cte_callday
group by 
    CUSTSYSREF
  , REPSSYSREF

image

Hi Harish,

You are basically doing the same normalisation - at runtime - in CTE's.
At the end his data structure stays flawed and as there has been no structural improvement of his data model.

Un Fortunately Wim
In Real Life Office Work Scenario's

We don't have any choices

Have to find solutions ... in fixed environments !!!
Would be Very Nice if we had a lot of flexibility and freedom to do WHAT WE think and WANT !!!

SELECT 
    T.CUSTSYSREF,
    T.REPSYSREF,
    MAX(CASE WHEN DayCounter = 1 THEN Day ELSE '' END) AS DayOfCall1,
    MAX(CASE WHEN DayCounter = 2 THEN Day ELSE '' END) AS DayOfCall2,
    MAX(CASE WHEN DayCounter = 3 THEN Day ELSE '' END) AS DayOfCall3,
    MAX(CASE WHEN DayCounter = 4 THEN Day ELSE '' END) AS DayOfCall4,
    MAX(CASE WHEN DayCounter = 5 THEN Day ELSE '' END) AS DayOfCall5,
    MAX(CASE WHEN DayCounter = 6 THEN Day ELSE '' END) AS DayOfCall6,
    MAX(CASE WHEN DayCounter = 7 THEN Day ELSE '' END) AS DayOfCall7
FROM #Table T
CROSS APPLY (
    SELECT *, ROW_NUMBER() OVER(ORDER BY DayNumber) AS DayCounter
    FROM ( VALUES(1, 'MON', MON), (2, 'TUE', TUE), (3, 'WED', WED), (4, 'THU', THU),
        (5, 'FRI', FRI), (6, 'SAT', SAT), (7, 'SUN', SUN) ) AS days(DayNumber, Day, DayValue)
    WHERE DayValue = 1
) AS ca1
GROUP BY T.CUSTSYSREF, T.REPSYSREF
ORDER BY CUSTSYSREF
1 Like