#sql-server
#sql-сервер
Вопрос:
Я хочу объединить нулевой столбец с тем же именем пользователя или идентификатором пользователя, где quarter отличается, например, если имя пользователя одинаково для quarter1, 2, 3, согласно приведенному ниже запросу, он показывает одно и то же имя пользователя с другим кварталом в другой строке, но я хочу в той же строке с другим кварталом и тем же именем пользователя
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
ЗАВЕРШЕНИЕ
Вперед
На этом изображении то же имя пользователя / идентификатор пользователя rahul имеет среднее значение в разных кварталах, как в q1-14 и так далее .. но в строке 1 оно имеет среднее значение в q1-14, после этого в строке 2 среднее значение равно q2-14 и так далее .. но я хочу удалить нулевой столбец и объединить строку 1/2/3, которая имеетто же имя пользователя, но среднее значение в другой строке, поэтому я хочу, чтобы оно было в одной строке.
итак, я хочу, как показано на рисунке ниже:
Комментарии:
1. вы также можете исключить значения null перед выбором таблицы, например: update #TEMPUSER set avg = isnull(avg, 0) я не использую команду Pivot, для меня я предпочитаю динамический sql для целей поворота таблицы
2. пожалуйста, покажите, какой результат вы ищете
Ответ №1:
Вы могли бы просто GROUP и SUM строки результирующей таблицы:
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
Для использования с вашим запросом вы можете поместить результаты во временную таблицу. Просто замените свой вызов на EXECUTE(@QUERY) приведенный ниже:
-- 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
Комментарии:
1. поскольку столбцы SUM (Q1-14), SUM (Q2-14), SUNM (Q4-13) являются динамическими, мы не можем этого сделать
2. @Mahesh это должно применяться к результирующей таблице, которая выводит эти столбцы, как указано в моем сообщении. Таким образом, запрос, который генерирует эту таблицу, будет выводиться во временную таблицу, а имя временной таблицы будет заменено
[TABLE]в моем запросе3. @Tanner я добавил еще одно изображение, какой вывод я хочу, а также добавил полную хранимую процедуру…
4. @Tanner: Я думаю, вы правы. перед выбором формы таблицы temptable нам нужно СГРУППИРОВАТЬ ПО ИДЕНТИФИКАТОРУ ПОЛЬЗОВАТЕЛЯ, ИМЕНИ пользователя этой таблицы
5. @Tanner, я ценю вашу помощь, но я поступил по-другому: передал простую таблицу в sql list и преобразовал этот список в datatable в коде позади,