如何排列没有空值的行。

问题描述

我有一个生成颜色表，我在下面用枢轴方法提到

I have a generate color table as I mention below with pivote methods

color1     color 2
Green       --
yellow      --
--          Black
--          White

我想要展示这个

I want to show this

color1     color 2
Green       Black
yellow      White

请帮忙解决它

Please help to resolve it

推荐答案

CREATE TABLE #TempColors (Color1 VARCHAR(50), Color2 VARCHAR(50));
 
INSERT INTO #TempColors (Color1, Color2)
VALUES ('Green', NULL), ('Yellow', NULL), (NULL, 'Black'), (NULL, 'White'), ('Pink', NULL);
 
SELECT Color1, Color2 FROM #TempColors;
 

;WITH IDs_CTE
     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID
         FROM #TempColors),
     Color1_CTE
     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, Color1
         FROM #TempColors
         WHERE Color1 IS NOT NULL),
     Color2_CTE
     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, Color2
         FROM #TempColors
         WHERE Color2 IS NOT NULL)
SELECT Color1_CTE.Color1, Color2_CTE.Color2
FROM   IDs_CTE
       LEFT OUTER JOIN Color1_CTE
                    ON Color1_CTE.ID = IDs_CTE.ID
       LEFT OUTER JOIN Color2_CTE
                    ON Color2_CTE.ID = IDs_CTE.ID
WHERE  Color1_CTE.Color1 IS NOT NULL OR Color2_CTE.Color2 IS NOT NULL
 

DROP TABLE #TempColors;

create table myColour
(
color1 varchar(20),
color2 varchar(20),
)

INSERT INTO myColour (color1) values ('White')
INSERT INTO myColour (color1) values ('Black')
INSERT INTO myColour (color2) values ('Yellow')
INSERT INTO myColour (color2) values ('Green')

SELECT * FROM myColour


--exec getMyColour
CREATE PROC getMyColour
AS
BEGIN

CREATE TABLE #tmpTableA(
	[ID] [INT] IDENTITY(1,1),
	[color1] [varchar](20)
)

INSERT INTO #tmpTableA SELECT color1 from myColour where isnull(color1,'') <> ''

CREATE TABLE #tmpTableB(
	[ID] [INT] IDENTITY(1,1),
	[color2] [varchar](20)
)

INSERT INTO #tmpTableB SELECT color2 from myColour where isnull(color2,'') <> ''


SELECT [color1],[color2] from #tmpTableA A, #tmpTableB B
where A.id=B.id

END

希望这会对你有所帮助。



干杯。

Hope this will help you.

Cheers.

这篇关于如何排列没有空值的行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！