I am using SQL Server Mgmt Studio
我正在使用SQL Server Mgmt Studio
I have not been able to find my answer on google or *, possibly due to phrasing.
我无法在谷歌或*上找到我的答案，可能是由于措辞。
What I am trying to do
我想做什么
I am trying to get there to be just one date with empty fields below it in the date (column1), but I'm also trying to group by column2 with a count in column3. I am also trying to get column4, column5, and column6 with different counts on the first row and blank fields underneath (similar to the date column).
我试图在日期（column1）中只有一个日期与其下面的空字段，但我也尝试按列列入第3列中的计数。我也试图让column4，column5和column6在第一行有不同的计数，下面有空白字段（类似于日期列）。
How I have tried to do it
我是怎么尝试这样做的
I have tried to use "partition by", but it just gives me a '1' in all the rows of my column, instead of just a count in the first row with the rest of the column left blank. I may not have written correctly, but I no longer have the code I used to give an example for that.
我试图使用“分区依据”，但它只是在我的列的所有行中给了我一个“1”，而不是仅在第一行中的一个计数，其余的列留空。我可能没有正确编写，但我不再拥有我曾经为此举例的代码。
I have also tried to do a subquery, but it gives me the same count through the entire column in every row.
我也试过做一个子查询，但它在每一行的整个列中都给了我相同的计数。
I am showing you my current output with the code I used to get it, along with my desired output.
我用我用来获取它的代码和我想要的输出向你显示我当前的输出。
My data currently looks like this:
我的数据目前看起来像这样：
Date | Bucket | #InBucket
2017-03-08 | NULL | 300
2017-03-08 | A | 27
2017-03-08 | B | 0
2017-03-08 | c | 4
2017-03-08 | D | 6
The query I used to get this data is:
我用来获取这些数据的查询是：
select convert(date, getdate(), 101) as [Date], dpcd.bucket, count(*) as [# in Bucket]
from mytable dpcd
where convert(date, getdate(), 101) = convert(date, deletedon, 101)
group by bucket
order by Bucket
My desired output is as follows:
我想要的输出如下：
Date | Bucket | #InBucket | column4 | column5 | colum6
2017-03-08 | NONE | 300 | 400 | 200 | 500
| A | 27 | | |
| B | 0 | | |
| c | 4 | | |
| D | 6 | | |
Long explanation, I apologize. Is what I am trying to do even possible? I don't have a lot of experience in SQL, so any and all help would be appreciated.
很久的解释，我道歉。我想做什么甚至可能？我没有很多SQL经验，所以任何和所有的帮助将不胜感激。
Is there already a question that has the answer I am searching for? If so, could you direct me to the question? I have not been able to find the answer.
是否有一个问题有我正在寻找的答案？如果是这样，你能指导我这个问题吗？我一直无法找到答案。
1 个解决方案
#1
1
You can do this in SQL, but such presentation-layer changes are better done in the application layer.
您可以在SQL中执行此操作，但在应用程序层中更好地完成此类表示层更改。
However, you can do it using row_number() and order by:
但是，您可以使用row_number（）并按顺序执行：
select (case when seqnum = 1 then [Date] end) as [Date],
bucket, [# in Bucket]
from (select convert(date, getdate(), 101) as [Date],
dpcd.bucket, count(*) as [# in Bucket],
row_number() over (order by bucket) as seqnum
from mytable dpcd
where convert(date, getdate(), 101) = convert(date, deletedon, 101)
group by bucket
) d
order by Bucket;
#1
1
You can do this in SQL, but such presentation-layer changes are better done in the application layer.
您可以在SQL中执行此操作，但在应用程序层中更好地完成此类表示层更改。
However, you can do it using row_number() and order by:
但是，您可以使用row_number（）并按顺序执行：
select (case when seqnum = 1 then [Date] end) as [Date],
bucket, [# in Bucket]
from (select convert(date, getdate(), 101) as [Date],
dpcd.bucket, count(*) as [# in Bucket],
row_number() over (order by bucket) as seqnum
from mytable dpcd
where convert(date, getdate(), 101) = convert(date, deletedon, 101)
group by bucket
) d
order by Bucket;