InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 03:07:02 GMT20http://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxHi all,Finally i was able to fix thisSorry for posting this in a incorrect thread.However I was able to solve it through using IIF function in accessThanks againAselaWed, 06 Mar 2013 04:41:15 GMTasela115http://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxNow 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.Thu, 28 Feb 2013 10:27:10 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxHi,thank you all for the responses given on thishowever 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 [B]ComboboxA[/B] Combo Box and refresh the related table it asks to enter [B]parameter value[/B] 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 doneanyway thanks for the help on this :)Thu, 28 Feb 2013 00:03:11 GMTasela115http://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxBased 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: 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.[code="sql"];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 FaultTypeFROM #TestTable AS tWHERE Year >= @Year)SELECT * FROM cteWHERE FaultType>''ORDER BY Year, MONTH[/code]Tue, 26 Feb 2013 06:33:56 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxOK, 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.[code="sql"]IF OBJECT_ID('dbo.FaultTable1') IS NOT NULL DROP TABLE dbo.FaultTable1IF OBJECT_ID('dbo.FaultTable2') IS NOT NULL DROP TABLE dbo.FaultTable2IF OBJECT_ID('dbo.FaultTable3') IS NOT NULL DROP TABLE dbo.FaultTable3CREATE 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 FaultTable1SELECT 2011,11,'Mfg Defect' UNION ALLSELECT 2012,4,'Mfg Defect' UNION ALLSELECT 2013,1,'Mfg Defect' INSERT INTO FaultTable2SELECT 2012,1,'Power Overload' UNION ALLSELECT 2013,1,'Power Overload' UNION ALLSELECT 2013,2,'Power Overload' INSERT INTO FaultTable3SELECT 2012,3,'User Error' UNION ALLSELECT 2012,4,'User Error' UNION ALLSELECT 2012,6,'User Error' [/code]Now create the schema-bound view:[code="sql"]CREATE VIEW dbo.vwFaultsWITH SCHEMABINDINGASSELECT [ID], [Year], [Month], [FaultType] FROM dbo.FaultTable1UNION ALLSELECT [ID], [Year], [Month], [FaultType] FROM dbo.FaultTable2UNION ALLSELECT [ID], [Year], [Month], [FaultType] FROM dbo.FaultTable3[/code]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.[code="sql"]DECLARE @FaultType VARCHAR(50)SET @FaultType = 'Power Overload'SELECT [ID], [Year], [Month], [FaultType] FROM dbo.vwFaultsWHERE FaultType = @FaultType AND ID > 0[/code]Sample output:[code="xml"]ID Year Month FaultType1 2012 1 Power Overload2 2013 1 Power Overload3 2013 2 Power Overload[/code] Mon, 25 Feb 2013 21:19:42 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxJust use the same code I posted above except change it around to suit your needs. [code="sql"]IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTableCREATE 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 #TestTableSELECT 2011,11,'Mfg Defect','','' UNION ALLSELECT 2012,1,'','Power Overload','' UNION ALLSELECT 2012,3,'','','User Error' UNION ALLSELECT 2012,4,'Mfg Defect','','' UNION ALLSELECT 2012,4,'','','User Error' UNION ALLSELECT 2012,6,'','','User Error' UNION ALLSELECT 2013,1,'','Power Overload','' UNION ALLSELECT 2013,1,'Mfg Defect','','' UNION ALLSELECT 2013,2,'','Power Overload','' --SELECT * FROM #TestTableDECLARE @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 = 2012SET @strSQL = 'SELECT t.[ID] ,t.[Year] ,t.[Month] ,'+@Response+' AS '+@Response+' FROM #TestTable AS tWHERE NULLIF('+@Response+','''') IS NOT NULL AND Year >= '+CAST(@Year AS VARCHAR(5))+'ORDER BY Year, MONTH'EXEC(@strSQL)[/code]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. Mon, 25 Feb 2013 19:02:13 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxthanks 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 requirementsee, I have a access table with column headings like below(say table1)year , month , feeder name , FaultType1 , FaultType2 , FaultType3now 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 requirementselect 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 chartmy 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 Table1I 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 selectionThanks for helping :-)Sun, 24 Feb 2013 19:10:51 GMTasela115http://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspx[quote][b]asela115 (2/24/2013)[/b][hr]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 assistRegards,asela115[/quote]I would use dynamic sql like this:First some sample data[code="sql"]IF OBJECT_ID('#TestTable') IS NOT NULL DROP TABLE #TestTableCREATE 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 #TestTableSELECT 111,1011,10001,789 UNIONSELECT 222,1022,10002,890 UNIONSELECT 333,1033,10003,789 UNIONSELECT 444,1044,10004,890 UNIONSELECT 555,1055,10005,789 UNIONSELECT 666,1066,10006,890 UNIONSELECT 777,1077,10007,789 UNIONSELECT 888,1088,10008,890 UNIONSELECT 999,1099,10009,678 SELECT * FROM #TestTable[/code]The code:[code="sql"]DECLARE @Response1 VARCHAR(50) ,@Response2 VARCHAR(50) ,@LocID INT ,@strSQL VARCHAR(MAX)SET @Response1 = 'EmplID'SET @Response2 = 'CustID'SET @LocID = 890SET @strSQL = 'SELECT t.ID ,'+@Response1+' AS '+@Response1+' ,'+@Response2+' AS '+@Response2+'FROM #TestTable AS tWHERE LocID = '+CAST(@LocID AS VARCHAR(5))+''EXEC(@strSQL)[/code]The output:[code="xml"]ID EmplID CustID2 222 100024 444 100046 666 100068 888 10008[/code]Sun, 24 Feb 2013 18:21:54 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxAssuming the same WHERE clause is used, you could use the CASE statement in your SELECT statement.Sun, 24 Feb 2013 12:56:22 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1423434-23-1.aspxHi,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 assistRegards,asela115Sun, 24 Feb 2013 11:23:08 GMTasela115