SQL透视和分组不完全正常工作 [英] SQL Pivoting and grouping not exactly working

问题描述

Hello All



我有一个简单的桌子，我想要转动，而且大多数情况下它的工作方式与旋转名称的工作方式有关但不是其余的。



这是我原始表格的一部分。

姓名DateWorked FullDay 
 Tom 05- May-09 1 
 Tom 06-May-09 1 
 Tom 07-May-09 1 
 Nancy 05-May-09 1 
 Nancy 07-May-09 1 
 James 07-May-09 1 
 Andrew 05-May-09 1 
 Andrew 06-May-09 1 
 Andrew 07-May-09 1 

< br $> b $ b



这是我的旋转代码。

  DECLARE   @ DynamicPivotQuery   AS   NVARCHAR （MAX）
  DECLARE   @ ColumnName   AS   NVARCHAR （MAX）
 
   -   获取PIVOT列的不同值 
  SELECT  @ ColumnName = ISNULL（ @ ColumnName  + ' ，'，' '）
 + QUOTENAME（name）
  FROM （选择 不同名称来自 tbldaysworked）  AS 课程
  order   by  name 
   -    print @columnname  
 
   -   选择dayworked，name，FullDay from tblDaysWorked group by dayworked，name，FullDay order by dayworked，name  
   -   使用动态 
准备PIVOT查询 SET   @DynamicPivotQuery  = 
 N '  SELECT dayworked，' +  @ ColumnName  + '  
 FROM tbldaysworked 
 PIVOT（max（fullday）
 FOR名称IN（' +  @ ColumnName  + ' ））AS PVTTable group by dayworked，' +  @ columnname  + ' 按日工作排序' 
  print   @ DynamicPivotQuery  
  EXEC  sp_executesql  @ DynamicPivotQuery  

这就是我得到的。我需要在那里的某个地方放一个小组，但在哪里？你看到我在哪里，但它没有用。如果我把它放在其他地方就会出错并且什么都不做。

 Tom Nancy James Andrew 
 05-May-09 1 
 --------------------------------------------- -  
 05-May-09 1 
 ----------------------------------- ----------- 
 05-May-09 1 
 ------------------------- --------------------- 
 06-May-09 1 
 --------------- ------------------------------- 
 06-May-09 1 
 ----- ----------------------------------------- 
 07-May-09 1 
 --------------------------------------------- -  
 07-May-09 1 
 ----------------------------------- ----------- 
 07-May-09 1 
 ------------------------- --------------------- 
 07-May-09 1 

另一件事是我不想依赖FullDay专栏中的1，因为有时会有半天存储在另一列中，但是对于休假日，无论你是工作一天还是一整天，解决了是一样的。



在pivot语句中是否有一种方法可以使用看起来像这样的用例

CASE WHEN（SELECT DateWorked .....然后1其他0结束？

 Tom Nancy James Andrew 
 05-May-09 1 1 0 1 
 06-May-09 1 0 0 1 
 07-May-09 1 1 1 1 

最后，我只是试图让我的数据透视表看起来像这样，1或0是基于人的名字旁边的日期，而不是FullDay列。



我试图从DimDate表中加入日期（为数据仓库创建和填充日期维度 [ ^ ]）。问题是我永远无法使其发挥作用。我想使用另一张桌子上的日期以防万一特定日期没有显示，因为这是每个人的休息日，如圣诞节或新年，但我仍然希望它显示为0.有关这方面的任何想法？有没有办法在变量日期之间显示日期，即@FromDate到@ToDate？



谢谢



Mike

解决方案

试试这个：

  CREATE  表 #tbldaysworked（名称 VARCHAR （ 30 ），DateWorked  DATETIME ，FullDay  INT ）
 
  INSERT   INTO  #tbldaysworked（Name，DateWorked，FullDay）
  VALUES （'  Tom'，'  05-May-09'， 1 ），
（  Tom'，'  06-May-09'， 1 ），
（'  Tom'，'  07- 09年5月'， 1 ），
（'  Nancy'，'  05-May-09'，< span class =code-digit> 1 ），
（'  Nancy'，'  07-May-09'， 1 ），
（'  James'，'  07-May-09'， 1 ），
 （'  Andrew'，'  05-May-09， 1 ），
（''  Andrew'，'  06-May-09'， 1 ），
（'  Andrew'，'  07-May-09'， 1 ）
 
  DECLARE   @cols  < span class =code-keyword> VARCHAR （ 300 ）
  DECLARE   @ dt   VARCHAR （ 2000 ）
  DECLARE   @ pt   VARCHAR （< span class =code-digit> 4000 ）
 
  SET   @cols  = STUFF（（ SELECT   DISTINCT  ' ]，[' + [Name] 
  FROM  #tbldaysworked 
  ORDER   BY  < span class =code-string>' ]，[' + [Name] 
  FOR  XML PATH（' '））， 1 ， 2 ，' '）+ ' ]' 
 
  SET   @ dt  = '  SELECT * 
 FROM #tbldaysworked' 
 
  SET   @ pt  = '  SELECT DateWorked，' +  @cols  + < span class =code-string>' ' + 
 ' < span class =code-string> FROM（' +  @ dt  + ' < span class =code-string>）AS DT' + 
 '  PIVOT（SUM （FullDay）FOR Name IN（' +  @cols  + ' ））AS PT' 
 
  EXEC （ @ pt ）
 
 
  DROP   TABLE  #tbldaysworked 

你看到了区别吗？



如需了解更多信息，请参阅：带有动态列的枢轴 [ ^ ]

Hello All

I have a simple table that I want to pivot and for the most part it's working as in the pivoting of the names works but not the rest.

Here's part of my original table.

Name	DateWorked	FullDay
Tom	05-May-09	1
Tom	06-May-09	1
Tom	07-May-09	1
Nancy	05-May-09	1
Nancy	07-May-09	1
James	07-May-09	1
Andrew	05-May-09	1
Andrew	06-May-09	1
Andrew	07-May-09	1

Here's my pivoting code.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(name)
FROM (select distinct name from tbldaysworked) AS Courses 
order by name
--print @columnname

 --select dayworked, name, FullDay from tblDaysWorked group by dayworked, name, FullDay order by dayworked, name
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT dayworked, ' + @ColumnName + '
    FROM tbldaysworked
    PIVOT(max(fullday)
          FOR name IN (' + @ColumnName + ')) AS PVTTable group by dayworked,' + @columnname + 'order by dayworked'
print @DynamicPivotQuery
EXEC sp_executesql @DynamicPivotQuery

Here's what I get. I need to put a group by somewhere in there but where? You see where I have it but it didn't work. If I put it anywhere else it errors out and does nothing.

                Tom	Nancy	James	Andrew
05-May-09	1			
----------------------------------------------
05-May-09		1		
----------------------------------------------
05-May-09				1
----------------------------------------------
06-May-09	1			
----------------------------------------------
06-May-09				1
----------------------------------------------
07-May-09	1			
----------------------------------------------
07-May-09		1		
----------------------------------------------
07-May-09			1	
----------------------------------------------
07-May-09				1

The other thing is that I don't want to rely getting the 1 from the FullDay column because sometimes there's half days that are stored in another column but for vacation days whether you work part of a day or a full day it works out to be the same.

Is there a way in the pivot statement to use case that would look something like this
CASE WHEN (SELECT DateWorked ..... then 1 else 0 end?

                Tom	Nancy	James	Andrew
05-May-09	1	1	0	1
06-May-09	1	0	0	1
07-May-09	1	1	1	1

In the end I'm just trying to make my pivot table look like this where the 1 or 0 is based on if there's a date beside the person's name and not from the FullDay column.

I tried to join the date from the DimDate table(Create and Populate Date Dimension for Data Warehouse[^]). The problem was I could never make it work. I want to use dates from another table just in case a specific date doesn't show because it was a day off for everyone like Christmas or New Year's Day but I still want it to show up as a 0. Any thoughts on that part? Is there a way to show a date between variable dates i.e. @FromDate to @ToDate?

Thanks

Mike

解决方案

Try this:

CREATE TABLE #tbldaysworked (Name VARCHAR(30), DateWorked DATETIME, FullDay INT)

INSERT INTO #tbldaysworked (Name, DateWorked, FullDay)
VALUES('Tom', '05-May-09',  1),
('Tom', '06-May-09',    1),
('Tom', '07-May-09',    1),
('Nancy', '05-May-09',  1),
('Nancy', '07-May-09',  1),
('James', '07-May-09',  1),
('Andrew', '05-May-09', 1),
('Andrew', '06-May-09', 1),
('Andrew', '07-May-09', 1)

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)

SET @cols = STUFF((SELECT DISTINCT '],[' + [Name]
            FROM #tbldaysworked
            ORDER BY  '],[' + [Name]
            FOR XML PATH('')), 1, 2, '') + ']'

SET @dt = 'SELECT *
            FROM #tbldaysworked'

SET @pt = 'SELECT DateWorked, ' + @cols + ' ' +
            'FROM (' + @dt + ') AS DT ' +
            'PIVOT(SUM(FullDay) FOR Name IN(' + @cols + ')) AS PT'

EXEC (@pt)


DROP TABLE #tbldaysworked

Do you see the difference?

For further information, please, see: Pivots with Dynamic Columns[^]

这篇关于SQL透视和分组不完全正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！