How to merge null column in sql table

Question

I want to merge null column with same username or userid where quarter is different, like if username is same for quarter1,2,3, according to below query it is showing same username with different quarter in different row, but i want in same row with different quarter and same user name

enter image description here

    ALTER PROCEDURE [dbo].[test]
--@USERID UNIQUEIDENTIFIER,
@BASESTARTDATE DATE,
@BASEENDDATE DATE,
@CASETYPEID VARCHAR(40),
@GROUPTYPE VARCHAR(40),
@COMPANYID VARCHAR(40),
@DEPTID VARCHAR(40)
   AS
    BEGIN
SET NOCOUNT ON

--CREATE TEMPORARY TABLE FOR INSERTING CALCULATED QUARTER
CREATE TABLE #QUARTERTEMP(QUARTER  VARCHAR(10),STARTDATE DATE,ENDDATE DATE,COUNTROW INT)

DECLARE @STARTDATETEMP DATETIME
DECLARE @ENDDATETEMP DATETIME
DECLARE @COUNTQUARTER INT
DECLARE @COUNTERCOUNT INT
SELECT @STARTDATETEMP=@BASESTARTDATE , @ENDDATETEMP=@BASEENDDATE
;WITH QUARTERS AS (
    SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@STARTDATETEMP),0) AS DT,
           DATEADD(DD, -1,DATEADD(QQ,DATEDIFF(QQ,0,@STARTDATETEMP)+1,0)) AS DATEEND
    UNION ALL
    SELECT DATEADD(QUARTER,1,DT),DATEADD(QUARTER,1,DATEEND) FROM QUARTERS
    WHERE DT < DATEADD(QUARTER,DATEDIFF(QUARTER,0,@ENDDATETEMP),0)

)
INSERT INTO #QUARTERTEMP
SELECT 'Q' + DATENAME(QQ,DT) + '-' + CAST(RIGHT(YEAR(DT)+ 0,2) AS VARCHAR(50)),DT AS 'STARTDATE',DATEEND AS 'ENDDATE',ROW_NUMBER() OVER (ORDER BY DT)FROM QUARTERS
--SELECT * FROM #QUARTERTEMP
/*DECLARE LOCAL VARIABLES*/
DECLARE @QUARTERNAME AS VARCHAR(10)
DECLARE @BEGINDATE AS DATE
DECLARE @ENDDATE AS DATE
SET @COUNTERCOUNT = 1
SELECT @COUNTQUARTER = COUNT(*) FROM #QUARTERTEMP
DECLARE @RESPONSEVALUE AS DECIMAL(9,1)
DECLARE @COUNT AS INT
DECLARE @ALLAVG  AS DECIMAL(9,2)

/*CREATE TEMP TABLE FOR INSERTING ALL RECORDS OF USERS*/
CREATE TABLE #TEMPUSER(USERID UNIQUEIDENTIFIER, USERNAME  VARCHAR(100),[QUARTER] VARCHAR(40) NULL,[AVG] DECIMAL (9,2) NULL,[AVGALL] DECIMAL (9,2) NULL)

    /*START OF WHILE LOOP*/
    WHILE (@COUNTERCOUNT <= @COUNTQUARTER)
    BEGIN

        /*FETCH QUARTERS TABLE QUARTERWISE*/
        SELECT @QUARTERNAME = [QUARTER],@BEGINDATE=STARTDATE,@ENDDATE = ENDDATE FROM #QUARTERTEMP WHERE COUNTROW = @COUNTERCOUNT
        print(@BEGINDATE)
        print(@ENDDATE)
        IF @DEPTID IS NOT NULL
        BEGIN
        PRINT('IN DEPT')
            /*DEPARTMENT AVG*/
            CREATE TABLE #TEMPALLUSER(USERID UNIQUEIDENTIFIER, USERNAME  VARCHAR(100),[QUARTER] VARCHAR(40) NULL,[AVG] DECIMAL (9,2) NULL,[AVGALL] DECIMAL (9,2) NULL)

            SELECT  @ALLAVG = ROUND(AVG(CAST(RESPONSEVALUE AS FLOAT)), 2) FROM SURVEYRESPONSES SR LEFT OUTER JOIN SURVEYS  S 
            ON SR.SURVEYID = S.SURVEYID 
            LEFT OUTER JOIN SURVEYTARGETS ST ON S.SURVEYTARGETID = ST.SURVEYTARGETID
            LEFT OUTER JOIN USERS U ON  ST.IDENTIFICATION = U.USERID
            INNER JOIN [DBO].[SURVEYDEFINITIONS] SD ON S.SURVEYDEFINITIONID=SD.SURVEYDEFINITIONID
            INNER JOIN [DBO].[PARENTCHILDS] PC ON SD.SURVEYDEFINITIONTYPEID=PC.CHILDID
            INNER JOIN [DBO].[MASTERQUESTIONS] M ON SR.MASTERQUESTIONID=M.MASTERQUESTIONID
            INNER JOIN [DBO].[QUESTIONGROUPS] Q ON M.QUESTIONGROUPID=Q.QUESTIONGROUPID
            --WHERE U.USERID IN (SELECT USERID FROM USERS WHERE USERS.PARENTUSERID = (SELECT TOP 1 PARENTUSERID FROM USERS WHERE USERS.IDENTIFICATION=@USERIDENTIFICATION)) 
            WHERE U.USERID IN (SELECT USERID FROM USERS WHERE USERS.PARENTUSERID = @DEPTID)
            AND (PC.PARENTID=@CASETYPEID OR PC.CHILDID=@CASETYPEID)
            AND (1=CASE WHEN ((@GROUPTYPE= '-1') OR (Q.QUESTIONGROUPID=@GROUPTYPE)) THEN 1 ELSE 0 END)
            AND SR.RESPONSEVALUE != -2147483648 AND S.SURVEYSTATUSID IN(6) AND S.SURVEYSTATUSID != 99
            AND CAST(S.RESPONSEDATE AS DATE) BETWEEN @BEGINDATE AND @ENDDATE

            /*INSERT VALUES IN TEMPALLUSER TABLE*/
            INSERT INTO #TEMPALLUSER
            SELECT U.USERID,U.NAME1,@QUARTERNAME,ROUND(AVG(CAST(RESPONSEVALUE AS FLOAT)), 2),@ALLAVG FROM SURVEYRESPONSES SR LEFT OUTER JOIN SURVEYS  S 
            ON SR.SURVEYID = S.SURVEYID 
            LEFT OUTER JOIN SURVEYTARGETS ST ON S.SURVEYTARGETID = ST.SURVEYTARGETID
            LEFT OUTER JOIN USERS U ON  ST.IDENTIFICATION = U.USERID
            INNER JOIN [DBO].[SURVEYDEFINITIONS] SD ON S.SURVEYDEFINITIONID=SD.SURVEYDEFINITIONID
            INNER JOIN [DBO].[PARENTCHILDS] PC ON SD.SURVEYDEFINITIONTYPEID=PC.CHILDID
            INNER JOIN [DBO].[MASTERQUESTIONS] M ON SR.MASTERQUESTIONID=M.MASTERQUESTIONID
            INNER JOIN [DBO].[QUESTIONGROUPS] Q ON M.QUESTIONGROUPID=Q.QUESTIONGROUPID
            --WHERE U.USERID IN (SELECT USERID FROM USERS WHERE USERS.PARENTUSERID = (SELECT TOP 1 PARENTUSERID FROM USERS WHERE USERS.IDENTIFICATION=@USERIDENTIFICATION)) 
            WHERE U.USERID IN (SELECT USERID FROM USERS WHERE USERS.PARENTUSERID = @DEPTID) AND 
            (PC.PARENTID=@CASETYPEID OR PC.CHILDID=@CASETYPEID)
            AND (1=CASE WHEN ((@GROUPTYPE= '-1') OR (Q.QUESTIONGROUPID=@GROUPTYPE)) THEN 1 ELSE 0 END)
            AND SR.RESPONSEVALUE != -2147483648 AND S.SURVEYSTATUSID IN(6) AND S.SURVEYSTATUSID != 99 
            AND CAST(S.RESPONSEDATE AS DATE) BETWEEN @BEGINDATE AND @ENDDATE
            GROUP BY U.USERID, U.NAME1
            ORDER  BY ROUND(AVG(CAST(RESPONSEVALUE AS FLOAT)), 2) DESC

            INSERT INTO #TEMPUSER
            SELECT * FROM #TEMPALLUSER  

            print(@COUNT)
            SELECT @COUNT = COUNT(*) FROM  #TEMPALLUSER
            IF @COUNT = 0
            BEGIN
                INSERT INTO #TEMPUSER VALUES(NULL,'',@QUARTERNAME,0.0,0.0)
            END
            DROP TABLE #TEMPALLUSER
        END /*END OF IF LOOP (DEPTID IS NOT NULL)*/

        ELSE /*START OF ELSE CONDITION*/
        BEGIN
            PRINT('IN COMP')            
            /*COMPANY AVG*/
            SELECT @ALLAVG = ROUND(AVG(CAST(RESPONSEVALUE AS FLOAT)), 2)  FROM SURVEYRESPONSES SR LEFT OUTER JOIN SURVEYS  S 
            ON SR.SURVEYID = S.SURVEYID 
            LEFT OUTER JOIN SURVEYTARGETS ST ON S.SURVEYTARGETID = ST.SURVEYTARGETID
            LEFT OUTER JOIN USERS U ON  ST.IDENTIFICATION = U.USERID
            INNER JOIN [DBO].[SURVEYDEFINITIONS] SD ON S.SURVEYDEFINITIONID=SD.SURVEYDEFINITIONID
            INNER JOIN [DBO].[PARENTCHILDS] PC ON SD.SURVEYDEFINITIONTYPEID=PC.CHILDID
            INNER JOIN [DBO].[MASTERQUESTIONS] M ON SR.MASTERQUESTIONID=M.MASTERQUESTIONID
            INNER JOIN [DBO].[QUESTIONGROUPS] Q ON M.QUESTIONGROUPID=Q.QUESTIONGROUPID
            --WHERE (U.PARENTUSERID IN (SELECT USERID FROM USERS WHERE USERS.PARENTUSERID = (SELECT PARENTUSERID FROM USERS WHERE USERS.USERID= (SELECT TOP 1 PARENTUSERID FROM USERS WHERE USERS.IDENTIFICATION=@USERIDENTIFICATION))) OR
            --U.PARENTUSERID = (SELECT PARENTUSERID FROM USERS WHERE USERS.USERID= (SELECT TOP 1 PARENTUSERID FROM USERS WHERE USERS.IDENTIFICATION=@USERIDENTIFICATION))) AND
            WHERE ((U.USERID IN (SELECT USERID FROM USERS WHERE PARENTUSERID IN (SELECT USERID FROM USERS WHERE PARENTUSERID =@COMPANYID)) 
            OR U.USERID IN (SELECT USERID FROM USERS WHERE PARENTUSERID=@COMPANYID))) AND 
            U.USERTYPEID = 3 AND (PC.PARENTID=@CASETYPEID OR PC.CHILDID=@CASETYPEID)
            AND (1=CASE WHEN ((@GROUPTYPE= '-1') OR (Q.QUESTIONGROUPID=@GROUPTYPE)) THEN 1 ELSE 0 END)
            AND SR.RESPONSEVALUE != -2147483648 AND S.SURVEYSTATUSID IN(6) AND S.SURVEYSTATUSID != 99
            AND CAST(S.RESPONSEDATE AS DATE) BETWEEN @BEGINDATE AND @ENDDATE

            /*INSERT VALUES IN TEMPALLUSER TABLE*/
            INSERT INTO #TEMPALLUSER
            SELECT U.USERID,U.NAME1,@QUARTERNAME,ROUND(AVG(CAST(RESPONSEVALUE AS FLOAT)), 2),@ALLAVG FROM SURVEYRESPONSES SR LEFT OUTER JOIN SURVEYS  S 
            ON SR.SURVEYID = S.SURVEYID 
            LEFT OUTER JOIN SURVEYTARGETS ST ON S.SURVEYTARGETID = ST.SURVEYTARGETID
            LEFT OUTER JOIN USERS U ON  ST.IDENTIFICATION = U.USERID
            INNER JOIN [DBO].[SURVEYDEFINITIONS] SD ON S.SURVEYDEFINITIONID=SD.SURVEYDEFINITIONID
            INNER JOIN [DBO].[PARENTCHILDS] PC ON SD.SURVEYDEFINITIONTYPEID=PC.CHILDID
            INNER JOIN [DBO].[MASTERQUESTIONS] M ON SR.MASTERQUESTIONID=M.MASTERQUESTIONID
            INNER JOIN [DBO].[QUESTIONGROUPS] Q ON M.QUESTIONGROUPID=Q.QUESTIONGROUPID
            --WHERE U.USERID IN (SELECT USERID FROM USERS WHERE USERS.PARENTUSERID = (SELECT TOP 1 PARENTUSERID FROM USERS WHERE USERS.IDENTIFICATION=@USERIDENTIFICATION)) 
            WHERE((U.USERID IN (SELECT USERID FROM USERS WHERE PARENTUSERID IN (SELECT USERID FROM USERS WHERE PARENTUSERID =@COMPANYID)) 
            OR U.USERID IN (SELECT USERID FROM USERS WHERE PARENTUSERID=@COMPANYID)))
            AND (PC.PARENTID=@CASETYPEID OR PC.CHILDID=@CASETYPEID)
            AND (1=CASE WHEN ((@GROUPTYPE= '-1') OR (Q.QUESTIONGROUPID=@GROUPTYPE)) THEN 1 ELSE 0 END)
            AND SR.RESPONSEVALUE != -2147483648 AND S.SURVEYSTATUSID IN(6) AND S.SURVEYSTATUSID != 99 
            AND CAST(S.RESPONSEDATE AS DATE) BETWEEN @BEGINDATE AND @ENDDATE
            GROUP BY U.USERID, U.NAME1
            ORDER  BY ROUND(AVG(CAST(RESPONSEVALUE AS FLOAT)), 2) DESC

            SELECT @COUNT = COUNT(*) FROM  #TEMPALLUSER
            IF @COUNT = 0
            BEGIN
                INSERT INTO #TEMPALLUSER VALUES(NULL,'',@QUARTERNAME,0.0,0.0)
            END

        END /*END OF IF ELSE LOOP*/
        SET @COUNTERCOUNT = @COUNTERCOUNT + 1
        SET @BEGINDATE = '1900-01-01'
        SET @ENDDATE = '1900-01-01'
    END /*END WHILE LOOP */

    /*FETCH DATA FROM TEMP TABLE*/
    DECLARE @COLS AS NVARCHAR(MAX),
        @QUERY  AS NVARCHAR(MAX);

    SELECT @COLS = STUFF((SELECT DISTINCT ',' + QUOTENAME(T.QUARTER) 
                        FROM #TEMPUSER T 
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

    SET @QUERY = 'SELECT DISTINCT USERID,USERNAME,AVGALL, ' + @COLS + ' FROM 
                 (
                     SELECT DISTINCT T.USERID
                            , T.USERNAME
                            ,T.AVGALL
                            , T.QUARTER
                            , T.AVG
                        FROM #TEMPUSER T

                ) X
                PIVOT 
                (
                    MIN(AVG)
                    FOR QUARTER IN (' + @COLS + ')
                ) P '

    EXECUTE(@QUERY)

    DROP TABLE #TEMPUSER

END

GO

In this image same username/userid rahul has avg in different quarters like in q1-14 and so on.. but in row 1 it has avg in q1-14, after that in row 2 avg is q2-14 and so on.. but i want remove null column and merge row 1/2/3 that has same user name but avg in different row, so i want it on single row.

so i want like this as below image:

you can also eliminate the null values before selecting the table, some thing like : update #TEMPUSER set avg = isnull(avg, 0) i don't use Pivot command, for me i prefer dynamic sql for table pivoting purposes — NeedAnswers
– NeedAnswers, Commented Jun 16, 2014 at 8:38

Tanner · Accepted Answer · 2014-06-16 10:14:32Z

1

You could just GROUP and SUM the rows of the resulting table:

SELECT USERID, USERNAME, MIN(AVGALL) as AVGALL, 
       SUM(Q1-14) as Q114, SUM(Q2-14) as Q214, SUNM(Q4-13) as Q413
FROM [TABLE]
GROUP BY USERID, USERNAME

To use with your query, you could push the results into a temp table. Just replace your call to EXECUTE(@QUERY) with the below:

-- select your results in to a temp table
SELECT INTO #T1 execute ('execute ' + @QUERY)

-- Perform the group and sum operations on the temp table to output results
SELECT USERID, USERNAME, MIN(AVGALL) as AVGALL, 
       SUM(Q1-14) as Q114, SUM(Q2-14) as Q214, SUNM(Q4-13) as Q413
FROM [#T1]
GROUP BY USERID, USERNAME

edited Jun 16, 2014 at 10:14

answered Jun 16, 2014 at 8:44

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

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

5 Comments

Mahesh Patel Over a year ago

as SUM(Q1-14), SUM(Q2-14), SUNM(Q4-13) columns are dynamic so we can't do that

Tanner Over a year ago

@Mahesh this is to apply to the resulting table that outputs those columns, which is stated in my post. So the query that generates that table would be output to a temp table and the temp table name would replace [TABLE] in my query

Ashok Dhakhada Over a year ago

@Tanner i've added one more image, what output i want and also added full stored procedure...

Mahesh Patel Over a year ago

@Tanner: I guess you'are right. before selecting form the temptable we need to GROUP BY USERID, USERNAME that table

Ashok Dhakhada Over a year ago

@Tanner, I appreciate your help, but i did on other way, pass simple table to sql list and convert that list to datatable in code behind,

Collectives™ on Stack Overflow

How to merge null column in sql table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related