sql - SQL - 在一个没有不规则空值的表中联合所有用户

Question

我希望空值位于列表的末尾，但将它们放在名称之间。

KullaniciAdi      KisiAdi
------------      ------------
AHMET GÜNDÜZ      " Ned Hsoleb(Guzel Alli) " --+
AHMET GÜNDÜZ      Raghuvar Masala              |
.                 .                            |
.                 .                            | AHMET GÜNDÜZ Has 10 Friends
.                 .                            |
.                 .                            |
AHMET GÜNDÜZ      Mint Agro -------------------+
ALİ AKKUM         Mustafa Saidmurodov -----+
ALİ AKKUM         Tarimkoop Ihsangazi      |
.                 .                        |
.                 .                        | ALİ AKKUM Has 9 Friends
.                 .                        |
.                 .                        |
ALİ AKKUM         Ali Uçar  ---------------+
YASEMİN AYSEN     Pepinier El Fertas --+
.                 .                    |
.                 .                    | YASEMİN AYSEN Has 6 Friends
.                 .                    |
YASEMİN AYSEN     Sarker Tredres ------+

这是我的查询联合 - 所有用户，但具有不规则的空值；

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); 
SET @cols = STUFF((SELECT distinct ',MAX(CASE WHEN KullaniciAdi = '''  + p.KullaniciAdi + ''' THEN KisiAdi END) AS '  + QUOTENAME(p.KullaniciAdi) FROM Populer p FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'') 
set @query = 'SELECT ' + @cols + ' FROM  (   SELECT KullaniciAdi,KisiAdi,ROW_NUMBER() OVER (PARTITION BY KullaniciAdi ORDER BY EklenmeTarihi) AS RowNum FROM Populer ) x GROUP BY RowNum ' 
EXECUTE(@query)

但是，我想要 ;

AHMET GÜNDÜZ                    ALİ AKKUM             YASEMİN AYSEN
------------                    ------------          -------------
" Ned Hsoleb(Guzel Alli) "      Mustafa Saidmurodov   Pepinier El Fertas
Raghuvar Masala                 Tarimkoop Ihsangazi   .
.                               .                     . 
.                               .                     .
.                               .                     .
.                               .                     Sarker Tredres
.                               .                     NULL
.                               .                     NULL
.                               Ali Uçar              NULL
Mint Agro                       NULL                  NULL

我无法编辑我的查询。你能帮我解决这个问题吗？

标签： sqlsql-server

Answer 1

你可以使用ROW_NUMBER()来达到效果。以下 SQL 将在末尾显示 NULL 值。我刚刚用ROW_NUMBER().

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); 
CREATE TABLE #tempPopuler (KullaniciAdi VARCHAR(50), KisiAdi VARCHAR(50))
INSERT INTO #tempPopuler VALUES('AHMET', 'Ned Hsoleb')
INSERT INTO #tempPopuler VALUES('AHMET', 'Raghuvar')
INSERT INTO #tempPopuler VALUES('AHMET', 'Mint Agro')
INSERT INTO #tempPopuler VALUES('ALI', 'Mustafa')
INSERT INTO #tempPopuler VALUES('ALI', 'Tarimkoop')
INSERT INTO #tempPopuler VALUES('YASEMIN', 'Pepinier')
INSERT INTO #tempPopuler VALUES('AHMET', 'A')
INSERT INTO #tempPopuler VALUES('AHMET', 'B')
INSERT INTO #tempPopuler VALUES('AHMET', 'C')
INSERT INTO #tempPopuler VALUES('ALI', 'A')
INSERT INTO #tempPopuler VALUES('ALI', 'B')
INSERT INTO #tempPopuler VALUES('YASEMIN', 'A')
INSERT INTO #tempPopuler VALUES('YASEMIN', 'B')

SET @cols = STUFF((SELECT distinct ',(SELECT KisiAdi FROM (SELECT ROW_NUMBER() OVER (ORDER BY KisiAdi ASC) AS rownumber, KullaniciAdi, KisiAdi FROM [#tempPopuler] where KullaniciAdi = '''  + p.KullaniciAdi + ''') AS foo WHERE rownumber = x.ROW and  KullaniciAdi ='''  + p.KullaniciAdi + ''') AS'  + QUOTENAME(p.KullaniciAdi) FROM #tempPopuler p FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'') 
set @query = 'SELECT ' + @cols + ' FROM  (SELECT KullaniciAdi,KisiAdi, ROW_NUMBER() OVER(ORDER BY KisiAdi ASC) AS [ROW] FROM #tempPopuler) x' 

EXECUTE(@query)

DROP TABLE #tempPopuler 

输出