各位大侠们:
我现在要做的是需要用一个View来实现把多个列中的数据整合到一个列中,除了使用Union all,还有其他的方法么?CREATE TABLE #TESTDATA
(
SourceIP VARCHAR(50),
DestIP VARCHAR(50),
OriginalClientIP VARCHAR(50)
)INSERT INTO #TESTDATA(SOURCEIP,DestIP,OriginalClientIP)
VALUES
('18108F1E-FFFF-0000-0000-000000000000','AC1F2D7A-FFFF-0000-0000-000000000000','836B004C-FFFF-0000-0000-000000000000'),
('18108F1F-FFFF-0000-0000-000000000000','AC1F2D7B-FFFF-0000-0000-000000000000','836B004D-FFFF-0000-0000-000000000000'),
('18108F1G-FFFF-0000-0000-000000000000','AC1F2D7C-FFFF-0000-0000-000000000000','836B004E-FFFF-0000-0000-000000000000')--Expect Result
IP
18108F1E-FFFF-0000-0000-000000000000
AC1F2D7A-FFFF-0000-0000-000000000000
836B004C-FFFF-0000-0000-000000000000
18108F1F-FFFF-0000-0000-000000000000
AC1F2D7B-FFFF-0000-0000-000000000000
836B004D-FFFF-0000-0000-000000000000
18108F1G-FFFF-0000-0000-000000000000
AC1F2D7C-FFFF-0000-0000-000000000000
836B004E-FFFF-0000-0000-000000000000--不适用Union All/Union
我现在要做的是需要用一个View来实现把多个列中的数据整合到一个列中,除了使用Union all,还有其他的方法么?CREATE TABLE #TESTDATA
(
SourceIP VARCHAR(50),
DestIP VARCHAR(50),
OriginalClientIP VARCHAR(50)
)INSERT INTO #TESTDATA(SOURCEIP,DestIP,OriginalClientIP)
VALUES
('18108F1E-FFFF-0000-0000-000000000000','AC1F2D7A-FFFF-0000-0000-000000000000','836B004C-FFFF-0000-0000-000000000000'),
('18108F1F-FFFF-0000-0000-000000000000','AC1F2D7B-FFFF-0000-0000-000000000000','836B004D-FFFF-0000-0000-000000000000'),
('18108F1G-FFFF-0000-0000-000000000000','AC1F2D7C-FFFF-0000-0000-000000000000','836B004E-FFFF-0000-0000-000000000000')--Expect Result
IP
18108F1E-FFFF-0000-0000-000000000000
AC1F2D7A-FFFF-0000-0000-000000000000
836B004C-FFFF-0000-0000-000000000000
18108F1F-FFFF-0000-0000-000000000000
AC1F2D7B-FFFF-0000-0000-000000000000
836B004D-FFFF-0000-0000-000000000000
18108F1G-FFFF-0000-0000-000000000000
AC1F2D7C-FFFF-0000-0000-000000000000
836B004E-FFFF-0000-0000-000000000000--不适用Union All/Union
(
SourceIP VARCHAR(50),
DestIP VARCHAR(50),
OriginalClientIP VARCHAR(50)
)
INSERT INTO #TESTDATA(SOURCEIP,DestIP,OriginalClientIP)
SELECT '18108F1E-FFFF-0000-0000-000000000000','AC1F2D7A-FFFF-0000-0000-000000000000','836B004C-FFFF-0000-0000-000000000000' UNION ALL
SELECT '18108F1F-FFFF-0000-0000-000000000000','AC1F2D7B-FFFF-0000-0000-000000000000','836B004D-FFFF-0000-0000-000000000000' UNION ALL
SELECT '18108F1G-FFFF-0000-0000-000000000000','AC1F2D7C-FFFF-0000-0000-000000000000','836B004E-FFFF-0000-0000-000000000000'
GO
--SQL:
SELECT b.IP
FROM #TESTDATA a
UNPIVOT
(IP FOR fieldname IN([SOURCEIP], [DestIP], [OriginalClientIP])) b
/*
IP
18108F1E-FFFF-0000-0000-000000000000
AC1F2D7A-FFFF-0000-0000-000000000000
836B004C-FFFF-0000-0000-000000000000
18108F1F-FFFF-0000-0000-000000000000
AC1F2D7B-FFFF-0000-0000-000000000000
836B004D-FFFF-0000-0000-000000000000
18108F1G-FFFF-0000-0000-000000000000
AC1F2D7C-FFFF-0000-0000-000000000000
836B004E-FFFF-0000-0000-000000000000
*/