Dynamic Select Query based on Query Column | SQL Server 2012

Question

May I ask on how do we execute Dynamic Select Query?

Basically what I want to achieve is a dynamic select that can SELECT a query based on a dynamic column, and that dynamic column is existing in a table.

Example Table

Table Name: AppleBox
Table Columns: Apple101, Apple102, Apple103
Table Row: 
Apple101 = 1,2,3,4,5
Apple102 = 1,2,
Apple103 = 1

Supposed that I would run a query based on the example

SELECT apple+'$applecode' FROM AppleBox

with $applecode being from an external source, and $applecode = 101, and my expected query would be.

SELECT apple101 FROM AppleBox

Is there a simple way to do this?

Fix your data model! You should have such columns in a table. Instead, you should have another table that has separate rows for each value. — Gordon Linoff, May 18 '21 at 11:01
@GordonLinoff what if I'm stuck with that format and I do not have a way/ not allowed to modify the table, would this be possible? — pjustindaryll, May 18 '21 at 11:03
I agree with what's been said here, the problem appears to be the design; fix that and likely the problem you have does not exist. — Thom A, May 18 '21 at 11:09
Does this answer your question? [Unpivot with column name](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) — DaFi4, May 18 '21 at 11:21
`and I do not have a way/ not allowed to modify the table` You cannot fix an issue if you are not allowed to make changes. Drop this project and move on to another client/project — GuidoG, May 18 '21 at 11:31

score 1 · Answer 1 · answered May 18 '21 at 12:14

Please check below code.

CREATE TABLE [dbo].[AppleBox](
    [Apple101] [varchar](50) NULL,
    [Apple102] [varchar](50) NULL,
    [Apple103] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[AppleBox] ([Apple101], [Apple102], [Apple103]) VALUES (N'1,2', N'1', N'1')
INSERT [dbo].[AppleBox] ([Apple101], [Apple102], [Apple103]) VALUES (N'3,4,5', N'1,2,', N'2')
INSERT [dbo].[AppleBox] ([Apple101], [Apple102], [Apple103]) VALUES (N'1,2,3,4,', N'1,2,3', N'3')
INSERT [dbo].[AppleBox] ([Apple101], [Apple102], [Apple103]) VALUES (N'1,2,3,4,5', N'1', N'4')


GO


DECLARE @query NVARCHAR(500)
DECLARE @exparam NVARCHAR(50)

SET @exparam='101'

SET @query='SELECT Apple'+@exparam+' FROM dbo.AppleBox'

EXECUTE sp_executesql  @query

What if Apple column will be expanded, is there a way for this to make it into a dynamic sql? — pjustindaryll, May 18 '21 at 14:22
I didn't get your question. this @exparam will be your external parameter. it can be upto 123456789..... (n digits) as we have taken as nvarchar(50) length — Ketan Kotak, May 19 '21 at 06:53

score 1 · Answer 2 · answered May 18 '21 at 12:41

1

You can use a case expression:

select (case when $applecode = 101 then apple101
             when $applecode = 102 then apple102
             when $applecode = 103 then apple103
        end) as apple
from t;

You would only need dynamic SQL (in this case) if your query could return a variable number of columns or if you wanted to set the name of the column. Neither seems important.

answered May 18 '21 at 12:41

Gordon Linoff

1,242,037
58
646
786

would there be a way for this? im worried because we might keep on expanding the SQL if there's more apple column to be added. :( – pjustindaryll May 18 '21 at 14:21
1

@pjustindaryll . . . Something is wrong with your data model if you are just adding new columns to a table. **These should be stored in separate rows.** – Gordon Linoff May 18 '21 at 18:19

Dynamic Select Query based on Query Column | SQL Server 2012

2 Answers2