Warm tip: This article is reproduced from serverfault.com, please click
sql sql-server

SQL query pivot, move values to top

发布于 2020-11-28 11:41:18

I've created a PIVOT query and the results are fine, however. I would like to flatten the rows so to speak and move all values to the top of the list and NULLS to the bottom.

http://sqlfiddle.com/#!18/7d17d/6

Instead of this:

+----------+----------+----------+----------+----------+
|    A     |    B     |    C     |    D     |    E     |
+----------+----------+----------+----------+----------+
| Report 1 | (null)   | (null)   | (null)   | (null)   |
| (null)   | Report 1 | (null)   | (null)   | (null)   |
| (null)   | (null)   | Report 2 | (null)   | (null)   |
| Report 3 | (null)   | (null)   | (null)   | (null)   |
| (null)   | (null)   | Report 4 | (null)   | (null)   |
| (null)   | Report 4 | (null)   | (null)   | (null)   |
| (null)   | (null)   | (null)   | Report 4 | (null)   |
| (null)   | (null)   | (null)   | (null)   | Report 5 |
| (null)   | Report 6 | (null)   | (null)   | (null)   |
| (null)   | (null)   | (null)   | (null)   | Report 7 |
| (null)   | (null)   | (null)   | (null)   | Report 8 |
+----------+----------+----------+----------+----------+

I would like to create something like this:

+----------+----------+----------+----------+----------+
|    A     |    B     |    C     |    D     |    E     |
+----------+----------+----------+----------+----------+
| Report 1 | Report 1 | Report 4 | Report 4 | Report 5 |
| Report 3 | Report 4 | Report 2 | (null)   | Report 7 |
| (null)   | Report 6 | (null)   | (null)   | Report 8 |
+----------+----------+----------+----------+----------+
Questioner
tutu
Viewed
0
分享
Gordon Linoff 2020-11-28 20:10:24

Change your ROW_NUMBER() to partition by the data source:

ROW_NUMBER() OVER (partition by datasource ORDER BY GETDATE()) AS rn

Your version is assigning a unique number to every row in the source data, which is why you get that number of rows back.

So the query is:

set @query = 'SELECT ' + @cols + '
from (select Name, DataSource,
             row_number() over (partition by datasource order by getdate()) AS rn
      from T
     ) x
pivot (max(Name) for DataSource in (' + @cols + ')
     ) p '

execute(@query);

Here is the fiddle.

tutu 2020-11-28 12:13:30

Thanks that worked! Is it also a simple feat to get the same report names on the same row?

Gordon Linoff 2020-11-28 14:15:53

@tut . . . I'm not sure what your comment means. Perhaps you should ask a new question with appropriate sample data and desired results.

热门github

1
A multi-platform library for OpenGL, OpenGL ES, Vulkan, window and input
2
Dev tool that writes scalable apps from scratch while the developer oversees the implementation
3
shadcn/ui, but for Svelte. ✨
4
The Python Risk Identification Tool for generative AI (PyRIT) is an open access automation framework to empower security professionals and machine learning engineers to proactively find risks in their generative AI systems.
5
Performance-portable, length-agnostic SIMD with runtime dispatch
6
ZK Credo
7
OpenCodeInterpreter: Integrating Code Generation with Execution and Refinement
8
Joplin - the secure note taking and to-do app with synchronisation capabilities for Windows, macOS, Linux, Android and iOS.
9
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.
10
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
11
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
12
🎓 Path to a free self-taught education in Computer Science!
13
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
14
A collective list of free APIs
15
📚 Freely available programming books