Dynamic SQL: how can I retrieve data from 3 different tables and count the results?

Question

I want to count the number of times people have selected a certain option in a choice field (represented as a -element with options on a webpage).

The name of choice fields are stored in the CodeType table.

> select [CodeTypeID] from [CodeType] where [Name] = 'Favorite Fruit'

CodetypeID
----------
1000210

I can find the choices by querying the CodytypeID-table by the found CodetypeID:

> select [ArtifactID], [Name] from [Code] where [CodetypeID] = '1000210'

ArtifactID   Name
-------------------
1039477     Apple
1039478     Pear
1039479     Banana
1039912     Peach

Each choice field has a separate table (named CodeArtifact_XXXX where XXXX is the CodetypeID found above). By counting the number of rows for each option (identified by CodeArtifactID) I can see how many times a choice was selected:

> select
>   count(case when [CodeArtifactID] = '1039477' then 1 else null end) as 'Apple'
>   count(case when [CodeArtifactID] = '1039478' then 1 else null end) as 'Pear'
>   count(case when [CodeArtifactID] = '1039479' then 1 else null end) as 'Banana'
>   count(case when [CodeArtifactID] = '1039477' then 1 else null end) as 'Peach'
> from
>   [CodeArtifact_1000210]

Apple   Pear    Banana    Peach
-------------------------------
12      0       7         4

However, I have to count the choices for many more choicefields. Therefore, hardcoding all of this is not an option.

I think the only solution here is to use dynamic SQL -- something I have no experience with. Could someone give me an example of how I could count for any choicefield, the number of times their options are chosen?

I other words: by only changing the choicefield [Name] in the beginning (Favorite Fruit, 'Marital Status', 'Latest Diploma', etc), the choices should be looked up and counted automatically.

Is this possible? Any help would be greatly appreciated.

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Tab Alleman, Mar 10 '16 at 21:48
Wow, that database design... A table like `CodeArtifact_1000210` should not exist. — Tom H, Mar 10 '16 at 21:56
i agree with Tom H - fix the design and the queries will be easier. — Randy, Mar 10 '16 at 22:04
I cannot fix the design. It is a propietary product and I am not allowed, nor have the user-rights to fix the design. I can only query against this database. Therefore I am asking for help :) — Pr0no, Mar 11 '16 at 01:35
@TomH what if those tables are code generated table name or it is multitenant database with table per account model? nothing wrong here until you know more details — vittore, Mar 11 '16 at 02:59

Abdullah Elen · Answer 1 · 2016-03-11T03:10:25.987

You can directly execute the following code..

    Declare
        @CodeName   nVarChar(100),
        @CodeType   nVarChar(100),
        @ColumnName nVarChar(MAX),
        @DynamicSQL nVarChar(MAX);


    Set @CodeName = 'Favorite Fruit';

    Select
       @CodeType = Convert(varChar, [CodetypeID])
    From
       [dbo].[CodeType]
    Where
       [Name] = @CodeName;

    Create Table #tmpCode
        (
            [ArtifactID]    int,
            [Name]          VarChar(100)
        )

Set @DynamicSQL =
    'Insert Into #tmpCode
        Select
            [ArtifactID],
            [Name]
        From
            [dbo].[CodeType]                As ct
                Inner Join  [dbo].[Code]    As cd
                    On ct.[CodetypeID] = cd.[CodetypeID]' +
                'Inner Join [dbo].[CodeArtifact_' + @CodeType + ']  As ca
                    On ca.[CodeArtifactID] = cd.[ArtifactID]
        Where
            ct.[Name] = @CodeName';

Exec(@DynamicSQL);


    Select @ColumnName =
        STUFF((
            Select
                ',' + QUOTENAME([Name])
            From
                #tmpCode
            Group By
                [Name]
        For XML Path(''), Type).value('.', 'nVarChar(MAX)'), 1, 1, '')


    Set @DynamicSQL =
        'Select ' + @ColumnName + ' From
            (Select [ArtifactID], [Name] From #tmpCode) tmp
            Pivot (Count([ArtifactID]) For [Name] In (' + @ColumnName + ')) As pvt ';

    Exec sp_executesql @DynamicSQL;
    -- Exec (@DynamicSQL);


    Drop Table
        #tmpCode

Dynamic SQL: how can I retrieve data from 3 different tables and count the results?

1 Answers1