How to make aggregate count based on functionname?

Transact-SQL
1

How to make aggregate count based on function name ?

I need to resume my query below or add code to my code below

in result below I need to aggregate by function Name

on first column CountDoneCode is make count to 1 based on function name

on second column CountNotDoneCode is make count to 0 based on function name

Final result I need is

  FunctionName	CountDoneCode	CountNotDoneCode
Lifecycle	3	0
Rohs	0	3
Reach	3	0
FMD	1	2
Parametric	1	2
Package	3	0
IntroductionDate	2	1
MFG	2	1
Qualification	2	1

my code below
create table #Donecode
(
FunctionId int,
FunctionName nvarchar(50)
)
insert into #Donecode
values
(1,'Lifecycle'),
(2,'Rohs'),
(3,'Reach'),
(4,'FMD'),
(5,'Parametric'),
(6,'Package'),
(7,'IntroductionDate'),
(8,'MFG'),
(9,'Qualification')

create table #filedetails
(
FileID  int,
DoneCode nvarchar(50)
)
insert into #filedetails (FileID,DoneCode) 
values 
(3301,'101011111'),
(3301,'101101111'),
(3301,'101001000')
select  substring (Donecode,1,1)as Lifecycle,substring (Donecode,2,1)as Rohs,substring (Donecode,3,1)as Reach,substring (Donecode,4,1)as FMD,substring (Donecode,5,1)as Parametric,substring (Donecode,6,1)as Package,substring (Donecode,7,1)as IntroductionDate,substring (Donecode,8,1)as MFG,substring (Donecode,9,1)as Qualification

into #FunctionsDiv  from #filedetails where DoneCode is not NULL and fileid=3301





declare @SeletColumnComma varchar(max)
select @SeletColumnComma = coalesce(@SeletColumnComma + ',','') + coalesce('substring(DoneCode ,' +cast (FunctionId as nvarchar(20)) + ',1) as ' + FunctionName + '','') from #Donecode
select @SeletColumnComma

DECLARE @query nvarchar(max)
SET @query='select ' + @SeletColumnComma  + '  from #filedetails where DoneCode is not null '
exec(@query)

drop table #filedetails
drop table #Donecode
drop table #FunctionsDiv
2

very difficult to make head or tails on your script. Help us help you and post clean and neat code?

select substring (Donecode,1,1)as Lifecycle,
       substring (Donecode,2,1)as Rohs,
	   substring (Donecode,3,1)as Reach,
	   substring (Donecode,4,1)as FMD,
	   substring (Donecode,5,1)as Parametric,
	   substring (Donecode,6,1)as Package,
	   substring (Donecode,7,1)as IntroductionDate,
	   substring (Donecode,8,1)as MFG,
	   substring (Donecode,9,1)as Qualification
into #FunctionsDiv  
from #filedetails 
where DoneCode is not NULL 
  and fileid=3301

instead of one looooooooooooooooooooong sql text that we have to parse and cleanup to understand it. Please clean it up so we can see it in one window like this?

Also it would help if you provided DROP TABLE

if OBJECT_ID('tempdb..#Donecode') is not null
	drop table #Donecode;
if OBJECT_ID('tempdb..#filedetails') is not null
	drop table #filedetails
1 Like
3

hi
i have formatted it using Red Gate SQL Prompt !!!

CREATE TABLE #Donecode
     (
         FunctionId INT
         , FunctionName NVARCHAR(50)
     );
BEGIN

    INSERT INTO #Donecode
    VALUES
        (1
         , 'Lifecycle')
        , (2
           , 'Rohs')
        , (3
           , 'Reach')
        , (4
           , 'FMD')
        , (5
           , 'Parametric')
        , (6
           , 'Package')
        , (7
           , 'IntroductionDate')
        , (8
           , 'MFG')
        , (9
           , 'Qualification');


    CREATE TABLE #filedetails
         (
             FileID INT
             , DoneCode NVARCHAR(50)
         );


    INSERT INTO #filedetails
         (
             FileID
             , DoneCode
         )
    VALUES
        (3301
         , '101011111')
        , (3301
           , '101101111')
        , (3301
           , '101001000');



    SELECT
        SUBSTRING(DoneCode, 1, 1) AS Lifecycle
        , SUBSTRING(DoneCode, 2, 1) AS Rohs
        , SUBSTRING(DoneCode, 3, 1) AS Reach
        , SUBSTRING(DoneCode, 4, 1) AS FMD
        , SUBSTRING(DoneCode, 5, 1) AS Parametric
        , SUBSTRING(DoneCode, 6, 1) AS Package
        , SUBSTRING(DoneCode, 7, 1) AS IntroductionDate
        , SUBSTRING(DoneCode, 8, 1) AS MFG
        , SUBSTRING(DoneCode, 9, 1) AS Qualification
    INTO
        #FunctionsDiv
    FROM
        #filedetails
    WHERE
        DoneCode IS NOT NULL
        AND FileID = 3301;

		
    DECLARE @SeletColumnComma VARCHAR(MAX);

	SELECT
        @SeletColumnComma = COALESCE(@SeletColumnComma + ',', '')
                            + COALESCE(
                                  'substring(DoneCode ,' + CAST(FunctionId AS NVARCHAR(20)) + ',1) as ' + FunctionName
                                  + '', '')
    FROM
        #Donecode;


    SELECT  @SeletColumnComma;

    DECLARE @query NVARCHAR(MAX);

    SET @query = N'select ' + @SeletColumnComma + N'  from #filedetails where DoneCode is not null ';

    EXEC (@query);

    DROP TABLE #filedetails;
    DROP TABLE #Donecode;
    DROP TABLE #FunctionsDiv;
END;
1 Like