每个列的每个不同的值
我想创建一个动态选择,它为宽表中的每个列返回每个不同的值。即每个列的每个不同的值
select distinct @mycolumn
from @mytable
为每一列和结果合并为一个表。
EDIT1:
EDIT2:返回的数据的顺序并不重要,源表可以有各种数据类型。
任何意见赞赏,谢谢!
我能想到的唯一方法是非常繁琐而且可能非常慢: 使用一个Tally表(为了这个答案,我使用递归cte生成了一个表,但这也不是一个很好的方法这样做...)和多个派生表左加入到该理货表我能够想出一些将产生所需的输出。然而,正如我在上面写的那样 - 它非常麻烦,可能非常慢(我只测试了5列和6行的表格,所以我不知道执行速度)。
DECLARE @Count int
select @Count = COUNT(1)
FROM YourTable
;with tally as (
select 1 as n
union all
select n + 1
from tally
where n < @Count
)
SELECT Column1, Column2, Column3, Column4, Column5
FROM tally
LEFT JOIN
(
SELECT Column1, ROW_NUMBER() OVER (ORDER BY Column1) rn
FROM
(
SELECT DISTINCT Column1
FROM YourTable
) t1
) d1 ON(n = d1.rn)
LEFT JOIN
(
SELECT Column2, ROW_NUMBER() OVER (ORDER BY Column2) rn
FROM
(
SELECT DISTINCT Column2
FROM YourTable
) t1
) d2 ON(n = d2.rn)
LEFT JOIN
(
SELECT Column3, ROW_NUMBER() OVER (ORDER BY Column3) rn
FROM
(
SELECT DISTINCT Column3
FROM YourTable
) t1
) d3 ON(n = d3.rn)
LEFT JOIN
(
SELECT Column4, ROW_NUMBER() OVER (ORDER BY Column4) rn
FROM
(
SELECT DISTINCT Column4
FROM YourTable
) t1
) d4 ON(n = d4.rn)
LEFT JOIN
(
SELECT Column5, ROW_NUMBER() OVER (ORDER BY Column5) rn
FROM
(
SELECT DISTINCT Column5
FROM YourTable
) t1
) d5 ON(n = d5.rn)
动态版:
DECLARE @TableName sysname = 'YourTableName'
DECLARE @Sql nvarchar(max) =
'
DECLARE @Count int
select @Count = COUNT(1)
FROM '+ @TableName +'
;with tally as (
select 1 as n
union all
select n + 1
from tally
where n < @Count
)
SELECT '
SELECT @Sql = @Sql + Column_Name +','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
SELECT @Sql = LEFT(@Sql, LEN(@Sql) - 1) + ' FROM tally t'
SELECT @Sql = @Sql + ' LEFT JOIN (SELECT '+ Column_Name +', ROW_NUMBER() OVER (ORDER BY ' + Column_Name +') rn
FROM
(
SELECT DISTINCT '+ Column_Name +' FROM '+ @TableName +') t
) c_'+ Column_Name + ' ON(n = c_'+ Column_Name + '.rn)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
EXEC(@Sql)
更新
在测试了22列和47000行的表,使用SQL Server 2014一个proper tally table.时,我建议把46秒 我很惊讶 - 我认为这将需要至少2-3分钟。
你快到了。现在动态地做。 :-P –
把它变成一个动态的sql并不难,但我怀疑它会比其他答案中Kahn提出的动态解决方案更好。 –
@JeroenMostert:挑战除外。 –
这是我正在研究的动态集。由于时间不够,所以没有清理干净,它会根据表中最大行数来确定动态行数,这意味着如果您在任何列中都有任何重复项,那么您将会留下行中每一列都为空的行。
但除此之外,这应该工作得很好,并且该脚本包含必要的信息,向您展示如何连接最终的“WHERE S1.COLNAME不为NULL且S2.COLNAME不为NULL并且..”过滤器结果表,以消除那些完全空行。
除此之外,这里是脚本。这显然很重,所以我在其中包含了一个(nolock)提示,并且“WHERE ColName不为空”来删除无用的结果。
试试在一张小桌子上看到它的工作。
/*
Set your table and schema on @MYTABLE and @MYSCHEMA variables.
*/
SET NOCOUNT ON
DECLARE @MYTABLE SYSNAME = 'Mytablename here'
, @MYSCHEMA sysname = 'dbo'
DECLARE @SQL NVARCHAR(MAX) = '', @COLNAME sysname = '', @MYCOLS NVARCHAR(max) = ''
DECLARE @COL_NOW INT = 1, @COL_MAX INT =
(SELECT COUNT(*)
FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables where name = @MYTABLE and SCHEMA_NAME(schema_id) = @MYSCHEMA))
SELECT @COLNAME = name
FROM sys.columns
WHERE column_id = 1
and object_id = (SELECT object_id FROM sys.tables where name = @MYTABLE and SCHEMA_NAME(schema_id) = @MYSCHEMA)
SET @SQL = 'FROM
(SELECT ROW_NUMBER() OVER (ORDER BY '[email protected]+' ASC) RN
FROM '[email protected]+'.'[email protected]+' (nolock)) S'
WHILE @COL_NOW <= @COL_MAX
BEGIN
SELECT @COLNAME = name
FROM sys.columns
WHERE column_id = @COL_NOW
and object_id = (SELECT object_id FROM sys.tables where name = @MYTABLE and SCHEMA_NAME(schema_id) = @MYSCHEMA)
SELECT @SQL = @SQL+'
FULL JOIN
(SELECT DISTINCT DENSE_RANK() OVER (ORDER BY '[email protected]+' ASC) RN, '[email protected]+'
FROM '[email protected]+'.'[email protected]+' (nolock)
WHERE '[email protected]+' IS NOT NULL) S'+CAST(@COL_NOW AS NVARCHAR(25))+' ON S'+CAST(@COL_NOW AS NVARCHAR(25))+'.RN = S.RN'
IF @COL_NOW = 1
SELECT @MYCOLS = @MYCOLS+' S'+CAST(@COL_NOW AS NVARCHAR(25))+'.'[email protected]
ELSE
SELECT @MYCOLS = @MYCOLS+', S'+CAST(@COL_NOW AS NVARCHAR(25))+'.'[email protected]
SET @COL_NOW = @COL_NOW+1
END
SELECT @SQL = 'SELECT'[email protected]+'
'[email protected]+'
ORDER BY S1.RN ASC';
--PRINT(@SQL); -- To check resulting dynamic SQL without executing it (Warning, print will only show first 8k characters)
EXEC sp_executesql @SQL;
GO
列的数据类型不会不同吗? – Heinzi
显示示例数据和所需结果。 –
我无法想象任何有用的场景。无论如何你将如何结合他们?我猜每列都有不同数量的不同值...... –