February 24, 2013 at 11:23 am
Hi,
How can i select columns of a table dynamically through a select statement if the column name need to be changed based on the value selected in a Combo Box in a different form.
it's like select column A from table X if combo box selection is A, select column B from same table if combo box selection is B etc..
Please assist
Regards,
asela115
February 24, 2013 at 12:56 pm
Assuming the same WHERE clause is used, you could use the CASE statement in your SELECT statement.
February 24, 2013 at 6:21 pm
asela115 (2/24/2013)
Hi,How can i select columns of a table dynamically through a select statement if the column name need to be changed based on the value selected in a Combo Box in a different form.
it's like select column A from table X if combo box selection is A, select column B from same table if combo box selection is B etc..
Please assist
Regards,
asela115
I would use dynamic sql like this:
First some sample data
IF OBJECT_ID('#TestTable') IS NOT NULL
DROP TABLE #TestTable
CREATE TABLE #TestTable (
ID INT IDENTITY(1,1) NOT NULL,
EmplID INT NULL,
MgrID INT NULL,
CustID INT NULL,
LocID INT NULL,
PRIMARY KEY (ID))
INSERT INTO #TestTable
SELECT 111,1011,10001,789 UNION
SELECT 222,1022,10002,890 UNION
SELECT 333,1033,10003,789 UNION
SELECT 444,1044,10004,890 UNION
SELECT 555,1055,10005,789 UNION
SELECT 666,1066,10006,890 UNION
SELECT 777,1077,10007,789 UNION
SELECT 888,1088,10008,890 UNION
SELECT 999,1099,10009,678
SELECT * FROM #TestTable
The code:
DECLARE
@Response1 VARCHAR(50)
,@Response2 VARCHAR(50)
,@LocID INT
,@strSQL VARCHAR(MAX)
SET @Response1 = 'EmplID'
SET @Response2 = 'CustID'
SET @LocID = 890
SET @strSQL = '
SELECT
t.ID
,'+@Response1+' AS '+@Response1+'
,'+@Response2+' AS '+@Response2+'
FROM
#TestTable AS t
WHERE
LocID = '+CAST(@LocID AS VARCHAR(5))+'
'
EXEC(@strSQL)
The output:
IDEmplIDCustID
222210002
444410004
666610006
888810008
February 24, 2013 at 7:10 pm
thanks you all for your prompt response. I'm so grateful for you all for spending your valuable time on assisting people like us.
let me clear my requirement as follows. I'm not going to do a sophisticated thing as it appears. sorry if my original post give an incorrect impression about my requirement
see, I have a access table with column headings like below(say table1)
year , month , feeder name , FaultType1 , FaultType2 , FaultType3
now I have created a form with combo boxes to select year month and FaultType etc... and i need to draw a pivot chart based on my selection in access form.
it's easy to get the combo box values for year, month(for instance combo value for Year will be 2011,2012 etc..). code like below meet that requirement
select Year from Table1 where Table1.Year=[Forms]![FormName]![Combo Name]etc..
however I need user to select FaultType through another combo box(in the same form) and then draw a pivot chart based on the FaultType he selected. my combo box drop down values should be FaultType1, Faultype2,FaultType3 which is infact the column names in my Table1 and once he select a specific FaultType then a pivot chart should be drawn based on that selection.
to do that I guess I have to write a SQL statement first to create another dynamic table based on user's selection and then draw the pivot chart
my SQL query will be like below one;
SELECT Year, Month, FeederName , "and the Fault type based on the user's selection through combo box" FROM Table1 WHERE etc...
Here the FaultType is actually the column names in my Table1
I need a help to fill the " " part in my above SQL which is on how to select the FaultType(1 or 2 or 3) from Table1 based on the users selection
Thanks for helping 🙂
February 25, 2013 at 7:02 pm
Just use the same code I posted above except change it around to suit your needs.
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
DROP TABLE #TestTable
CREATE TABLE #TestTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NULL,
[Month] INT NULL,
[FaultType1] VARCHAR(50) NULL,
[FaultType2] VARCHAR(50) NULL,
[FaultType3] VARCHAR(50) NULL,
PRIMARY KEY (ID))
INSERT INTO #TestTable
SELECT 2011,11,'Mfg Defect','','' UNION ALL
SELECT 2012,1,'','Power Overload','' UNION ALL
SELECT 2012,3,'','','User Error' UNION ALL
SELECT 2012,4,'Mfg Defect','','' UNION ALL
SELECT 2012,4,'','','User Error' UNION ALL
SELECT 2012,6,'','','User Error' UNION ALL
SELECT 2013,1,'','Power Overload','' UNION ALL
SELECT 2013,1,'Mfg Defect','','' UNION ALL
SELECT 2013,2,'','Power Overload',''
--SELECT * FROM #TestTable
DECLARE
@Response VARCHAR(50)
,@Year INT
,@strSQL VARCHAR(MAX)
SET @Response = 'FaultType1'
SET @Year = 2012
--SET @Response = 'FaultType2'
--SET @Year = 2011
--SET @Response = 'FaultType3'
--SET @Year = 2012
SET @strSQL = '
SELECT
t.[ID]
,t.[Year]
,t.[Month]
,'+@Response+' AS '+@Response+'
FROM
#TestTable AS t
WHERE
NULLIF('+@Response+','''') IS NOT NULL
AND Year >= '+CAST(@Year AS VARCHAR(5))+'
ORDER BY
Year, MONTH
'
EXEC(@strSQL)
The point to take away from this is that to pass in metadata such as table or column names requires some dynamic sql somewhere. If this is going to occur a lot it MIGHT be beneficial to put the table and column mappings into a separate table then join on that. If you know the column names already and they are limited in number I think the dynamic sql approach is as good as any.
February 25, 2013 at 9:19 pm
OK, I figured out another way to do this where you can pass in the column by variable. This method is called a Partitioned View. It requires that you set up a separate table for each fault status. So in your case where you now have three columns in one table you would have three tables with a single 'fault' column instead.
THEN...you create a Partitioned View that combines the three tables. If you set up the tables and related view correctly you will be able to pass in a variable to get the results from the proper column.
OK, first set up the tables and populate with test data. NOTE: The CHECK constraints are the key to this whole process.
IF OBJECT_ID('dbo.FaultTable1') IS NOT NULL
DROP TABLE dbo.FaultTable1
IF OBJECT_ID('dbo.FaultTable2') IS NOT NULL
DROP TABLE dbo.FaultTable2
IF OBJECT_ID('dbo.FaultTable3') IS NOT NULL
DROP TABLE dbo.FaultTable3
CREATE TABLE dbo.FaultTable1 (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NOT NULL,
[Month] INT NULL,
[FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'Mfg Defect'),
PRIMARY KEY ([ID],[Year]))
CREATE TABLE dbo.FaultTable2 (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NOT NULL,
[Month] INT NULL,
[FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'Power Overload'),
PRIMARY KEY ([ID],[Year]))
CREATE TABLE dbo.FaultTable3 (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NOT NULL,
[Month] INT NULL,
[FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'User Error'),
PRIMARY KEY ([ID],[Year]))
INSERT INTO FaultTable1
SELECT 2011,11,'Mfg Defect' UNION ALL
SELECT 2012,4,'Mfg Defect' UNION ALL
SELECT 2013,1,'Mfg Defect'
INSERT INTO FaultTable2
SELECT 2012,1,'Power Overload' UNION ALL
SELECT 2013,1,'Power Overload' UNION ALL
SELECT 2013,2,'Power Overload'
INSERT INTO FaultTable3
SELECT 2012,3,'User Error' UNION ALL
SELECT 2012,4,'User Error' UNION ALL
SELECT 2012,6,'User Error'
Now create the schema-bound view:
CREATE VIEW dbo.vwFaults
WITH SCHEMABINDING
AS
SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM dbo.FaultTable1
UNION ALL
SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM dbo.FaultTable2
UNION ALL
SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM dbo.FaultTable3
Finally, you can query this view and pass in the FaultType as a variable. Look at the query plan and you will see that it does the union of the three tables with a clustered index seek.
DECLARE @FaultType VARCHAR(50)
SET @FaultType = 'Power Overload'
SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM
dbo.vwFaults
WHERE
FaultType = @FaultType
AND ID > 0
Sample output:
IDYearMonthFaultType
120121Power Overload
220131Power Overload
320132Power Overload
February 26, 2013 at 6:33 am
Based on Stevens easy to use sample data (STRONG hint for future questions... 😉 ) here's my approach. The main difference is the "depending column" having a static header ("FaultType", similar to Stevens approach using three tables).
The reasons why I wouldn't use one of Stevens approaches:
a) dynamic SQL: I'm trying to avoid it where it's not absolutely required since it makes the code harder to read (especially if there are multiple quotes involved) and there's a risk of SQL injection that needs to be taken care of. @steven-3: I'd always use sp_executesql in such a scenario instead of exec().
b) separaet tables combined in a union-based view: this concept violates normalization and would require additional table(s) if there are new fault types. I'd strongly vote against such a concept.
;
WITH cte as
(
SELECT
t.[ID]
,t.[Year]
,t.[Month]
, CASE WHEN @Response = 'FaultType1' THEN FaultType1
WHEN @Response = 'FaultType2' THEN FaultType2
WHEN @Response = 'FaultType3' THEN FaultType3
END AS FaultType
FROM
#TestTable AS t
WHERE Year >= @Year
)
SELECT * FROM cte
WHERE FaultType>''
ORDER BY Year, MONTH
February 28, 2013 at 12:03 am
Hi,
thank you all for the responses given on this
however I came to know that ACCESS SQL doesn't support case when statements. is that correct?
further, during past few days I was looking for many options and found that IIF function too can be used to handle this sort of situation.
for instance;
select Year, Month,iif(ComboboxA = 'FT1',FaultType1,iif(ComboboxA = 'FT2',FaultType2,FaultType3)) as FaultType from Table1.
however each time when i make selection in my ComboboxA Combo Box and refresh the related table it asks to enter parameter value this Combo Box.
(I have attached the error message with this)
Any idea on why it asks to enter such parameter?
I f I can overcome this issue I guess my work is done
anyway thanks for the help on this 🙂
February 28, 2013 at 10:27 am
Now that is a slightly different story...
You asked a question in a SQL Server forum without even mentioning you're talking about MS Access. 🙁
You might find someone around answering your question. But that would be accidentially. You might want to ask in a MS Access related forum.
March 6, 2013 at 4:41 am
Hi all,
Finally i was able to fix this
Sorry for posting this in a incorrect thread.
However I was able to solve it through using IIF function in access
Thanks again
Asela
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply