January 4, 2006 at 11:28 am
Just need some advice or a solution on what I should do. I have two tables
Table 1 -- ApptFields
ColumName|IsAvailable
Referral|1
Balance|1
Remark|0
Home|0
DOB|1
Table 2 -- Customer
CustomerID|Name|DOB|Remark|Balance|Referral|Home
1|Bob Smith|01/20/1984|NULL|25.00|NULL|7885
2|Mike James|NULL|Lucky7|NULL|NULL|6211
What I would like to do is create a select statment that returns the following:
Depending on the fields in ApptFields that have a 1 for IsAvailable
I would like to see that data for those fields only.
So from the above tables the query would return:
CustomerID|Name|Refer|Balance|DOB --as my column name
1|Bob Smith|NULL|25.00|01/20/1984
2|Mike James|NULL|NULL|NULL
Thank You
January 4, 2006 at 12:09 pm
G'Day,
This might work...
CREATE TABLE ApptFields (
ColumnName VARCHAR(25),
IsAvailable INT
)
GO
INSERT INTO ApptFields (ColumnName, IsAvailable)
SELECT 'Referral', 1 UNION ALL
SELECT 'Balance', 1 UNION ALL
SELECT 'Remark', 0 UNION ALL
SELECT 'Home', 0 UNION ALL
SELECT 'DOB', 1
GO
CREATE TABLE Customer (
CustomerID INT,
Name VARCHAR(25),
DOB DATETIME,
Remark VARCHAR(25),
Balance INT,
Referral INT,
Home INT
)
GO
INSERT INTO Customer (CustomerID,Name,DOB,Remark,Balance,Referral,Home)
SELECT 1, 'Bob Smith', '01/20/1984', NULL, 25, NULL, 7885 UNION ALL
SELECT 2, 'Mike James', NULL, 'Lucky7', NULL, NULL, 6211
GO
DECLARE @MySQL VARCHAR(2000)
SELECT @MySQL = COALESCE(@MySQL + ', ', '') + ColumnName
FROM ApptFields
WHERE IsAvailable = 1
SET @MySQL = 'SELECT CustomerID, Name, ' + @MySQL + ' FROM Customer'
PRINT '<' + @MySQL + '>'
EXEC (@MySQL)
Hope this helps
Wayne
January 5, 2006 at 7:42 am
Thanks Wayne that worked perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply