count in dynamic name SQL Server

Question

I create a stored procedure in SQL Server:

ALTER PROCEDURE [dbo].[testChildren]
    @Parent INT
AS
BEGIN
    SET NOCOUNT ON;

    WITH EntityChildren AS
    (
         SELECT 
             nname, nodeid, level, ncode, nparent
         FROM 
             GrpItm
         WHERE 
             nodeid = @Parent

        UNION ALL

        SELECT 
            e.nname, e.nodeid, e.level, e.ncode, e.nparent
        FROM 
            GrpItm e
        INNER JOIN 
            EntityChildren e2 ON e.nparent = e2.nodeid
    )
    SELECT COUNT(Level) AS [level]
    FROM EntityChildren 
END

How can I make each count level in row and named the row by level value like this:

| level 1 | level 2 | level 3 |
+---------+---------+---------+
|    2    |    3    |    1    |

You can't have a dynamic number of columns in SQL. The only way to achieve that is to have code that writes the SQL for you, then execute that dynamically generated SQL. (Look up dynamic-sql and use of spexecute_sql.) — MatBailie
– MatBailie, Commented Nov 30, 2017 at 11:12
When things descends in stuff like "dynamic columns" most of times it's not a sql problem but a logic problem in the way you consume the DB and present data to user. Those kind of problem is, in general, better addressed in BL and UX layers — jean
– jean, Commented Nov 30, 2017 at 11:20

MatBailie · Accepted Answer · 2017-11-30 11:15:47Z

1

If you want something closer to...

 level | count
-------+-------
    1  |   2
    2  |   3
    3  |   1

Then it would just be something like...

SELECT
    level,
    COUNT(*)    AS row_count
FROM
    EntityChildren
GROUP BY
    level

But that's simpler than what you're already accomplished, so I may be missing the point of your question and comment?

answered Nov 30, 2017 at 11:15

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Abd ul rahaman Shalata Over a year ago

I can do this how make column to be rows

MatBailie Over a year ago

I don't understand what you just asked. You asked a question and in my comment I explained that you can't without "code-that-writes-sql" (dynamic-sql), and even gave you the key words to search for. You then asked me can i make column rows??, which I assumed was asking for the answer I just gave. So, I re-itterate, you can't get what you wanted in your original question in normal SQL...

MatBailie Over a year ago

If you want 3 columns (for counts over 3 levels) then you need a query with 3 expressions in the SELECT. If you want 4 columns (for counts over 4 levels) then you need a query with 4 expressions in the SELECT. Each time you want another column, you need a different query to be written. There are no SQL queries that can change the number of columns they return dependent on the data. That's what rows are for, and why relational databases are structured (normalised) as they are. You're trying to do something SQL and relational databases are not designed to do.

SqlKindaGuy Over a year ago

Nice answer - If he really wants to make it complicated he can take your result and unpivot it dynamically. But thats another story.

Abd ul rahaman Shalata Over a year ago

@plaidDK how can i do it?

|

SqlKindaGuy · Accepted Answer · 2017-11-30 13:52:21Z

This is only regarding to @MatBailie answer. If you have a table like his, you can pivot your data and get it as you request.

Table Created with Levels and counts

You need a script to populate this table with the values you need

create table dbo.myt (levels int, counts int)

insert into dbo.myt
values 
    (1 , 2),
    (2 , 3),
    (3 , 1)

SQL Code

DECLARE @ALIASNAME nvarchar(50) = 'Levels'
    DECLARE @Str NVARCHAR(MAX);
DECLARE @Str2 NVARCHAR(MAX);
SELECT @Str = STUFF(
                   (
                       SELECT DISTINCT
                              ','+'[Levels'+cast(levels as nvarchar(50))+']'
                       FROM dbo.myt FOR XML PATH('')
                   ), 1, 1, '');
PRINT @Str
SET @str2 = N'select * from (
   select cast('''+@ALIASNAME+'''+ cast(Levels as nvarchar(50)) as nvarchar(50)) as Levels,counts,ROW_NUMBER() over(PARTITION by levels order by counts) as rn
   from dbo.myt
   )x
   PIVOT 
   (Max(counts) FOR Levels in ('+@Str+')
   ) as p';
PRINT @Str2;
EXEC (@Str2);

Your Stored proc

ALTER PROCEDURE [dbo].[testChildren]
    @Parent INT
AS
BEGIN
    SET NOCOUNT ON;

    WITH EntityChildren AS
    (
         SELECT 
             nname, nodeid, level, ncode, nparent
         FROM 
             GrpItm
         WHERE 
             nodeid = @Parent

        UNION ALL

        SELECT 
            e.nname, e.nodeid, e.level, e.ncode, e.nparent
        FROM 
            GrpItm e
        INNER JOIN 
            EntityChildren e2 ON e.nparent = e2.nodeid
    )

    INSERT INTO dbo.myt (Levels,Counts)
    SELECT
    level, COUNT(*)    AS row_count
    FROM
    EntityChildren
    GROUP BY level 


    /* INSERT PIVOT SCRIPT AND INSERT INTO A NEW TABLE */

END

Result

Collectives™ on Stack Overflow

count in dynamic name SQL Server

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related