Обновить новое решение: проверить индекс
let a = ['a', 'a', 'b', 'a'];
let a = ['a', 'a', 'a', 'a'];
let check = (list) => list.every(item => list.indexOf(item) === 0);
check(a); // false;
check(b); // true;
Обновлено с ES6: Использовать list.every
- самый быстрый способ:
let a = ['a', 'a', 'b', 'a'];
let check = (list) => list.every(item => item === list[0]);
старая версия:
var listTrue = ['a', 'a', 'a', 'a'];
var listFalse = ['a', 'a', 'a', 'ab'];
function areWeTheSame(list) {
var sample = list[0];
return (list.every((item) => item === sample));
}
Динамический SQL PIVOT:
create table temp
(
date datetime,
category varchar(3),
amount money
)
insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT date, ' + @cols + ' from
(
select date
, amount
, category
from temp
) x
pivot
(
max(amount)
for category in (' + @cols + ')
) p '
execute(@query)
drop table temp
Результаты:
Date ABC DEF GHI
2012-01-01 00:00:00.000 1000.00 NULL NULL
2012-02-01 00:00:00.000 NULL 500.00 800.00
2012-02-10 00:00:00.000 NULL 700.00 NULL
2012-03-01 00:00:00.000 1100.00 NULL NULL
Ниже приведен код, который заменяет NULL на ноль в выходном файле.
Создание таблицы и вставка данных:
create table test_table
(
date nvarchar(10),
category char(3),
amount money
)
insert into test_table values ('1/1/2012','ABC',1000.00)
insert into test_table values ('2/1/2012','DEF',500.00)
insert into test_table values ('2/1/2012','GHI',800.00)
insert into test_table values ('2/10/2012','DEF',700.00)
insert into test_table values ('3/1/2012','ABC',1100.00)
Запрос для генерации точных результатов, который также заменяет NULL с нулями:
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(category) + ', 0) AS '
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT date, ' + @PivotSelectColumnNames + '
FROM test_table
pivot(sum(amount) for category in (' + @PivotColumnNames + ')) as pvt';
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
OUTPUT:
Вы можете добиться этого, используя динамический TSQL (не забудьте использовать QUOTENAME, чтобы избежать атак SQL-инъекций):
Свертывает динамические столбцы в SQL Server 2005
SQL Server - динамическая таблица PIVOT - SQL Injection
Обязательная ссылка на Проклятие и благословения динамического SQL
QUOTENAME
только помогает атакам SQL-инъекций, если вы принимаете @tableName в качестве параметра от пользователя и добавляете его к запросу, например SET @sql = 'SELECT * FROM ' + @tableName;
. Вы можете создать множество уязвимых динамических строк SQL, а QUOTENAME
не будет делать лизать, чтобы помочь вам.
– Aaron Bertrand
1 May 2012 в 22:13
Мое решение очистит ненулевые нулевые значения
DECLARE @cols AS NVARCHAR(MAX),
@maxcols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(CodigoFormaPago)
from PO_FormasPago
order by CodigoFormaPago
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @maxcols = STUFF((SELECT ',MAX(' + QUOTENAME(CodigoFormaPago) + ') as ' + QUOTENAME(CodigoFormaPago)
from PO_FormasPago
order by CodigoFormaPago
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT CodigoProducto, DenominacionProducto, ' + @maxcols + '
FROM
(
SELECT
CodigoProducto, DenominacionProducto,
' + @cols + ' from
(
SELECT
p.CodigoProducto as CodigoProducto,
p.DenominacionProducto as DenominacionProducto,
fpp.CantidadCuotas as CantidadCuotas,
fpp.IdFormaPago as IdFormaPago,
fp.CodigoFormaPago as CodigoFormaPago
FROM
PR_Producto p
LEFT JOIN PR_FormasPagoProducto fpp
ON fpp.IdProducto = p.IdProducto
LEFT JOIN PO_FormasPago fp
ON fpp.IdFormaPago = fp.IdFormaPago
) xp
pivot
(
MAX(CantidadCuotas)
for CodigoFormaPago in (' + @cols + ')
) p
) xx
GROUP BY CodigoProducto, DenominacionProducto'
t @query;
execute(@query);
Разный подход для создания столбцов string
create table #temp
(
date datetime,
category varchar(3),
amount money
)
insert into #temp values ('1/1/2012', 'ABC', 1000.00)
insert into #temp values ('2/1/2012', 'DEF', 500.00)
insert into #temp values ('2/1/2012', 'GHI', 800.00)
insert into #temp values ('2/10/2012', 'DEF', 700.00)
insert into #temp values ('3/1/2012', 'ABC', 1100.00)
DECLARE @cols AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end
set @query =
'SELECT * from
(
select date, amount, category from #temp
) src
pivot
(
max(amount) for category in (' + @cols + ')
) piv'
execute(@query)
drop table #temp
Результат
date ABC DEF GHI
2012-01-01 00:00:00.000 1000.00 NULL NULL
2012-02-01 00:00:00.000 NULL 500.00 800.00
2012-02-10 00:00:00.000 NULL 700.00 NULL
2012-03-01 00:00:00.000 1100.00 NULL NULL
Я знаю, что этот вопрос старше, но я искал ответы и думал, что я могу расширить «динамическую» часть проблемы и, возможно, помочь кому-то.
Прежде всего Я построил это решение для решения проблемы, с которой несколько сотрудников столкнулись с непостоянными и большими наборами данных, которые нужно быстро поворачивать.
Это решение требует создания хранимой процедуры, поэтому, если это неясно для ваших нужд, пожалуйста, прекратите читать сейчас.
Эта процедура будет принимать ключевые переменные сводной инструкции для динамического создания сводных операторов для разных таблиц, имен столбцов и агрегатов. Столбец Static используется как столбец / идентификатор группы для сводной таблицы (это может быть удалено из кода, если это не необходимо, но довольно часто используется в сводных операторах и было необходимо для решения исходной проблемы), столбец столбца - это то, где конечные имена столбцов будут генерироваться, а столбец значений - это то, к чему будет применяться совокупность. Параметр Table - это имя таблицы, включая схему (schema.tablename), эта часть кода может использовать некоторую любовь, потому что она не такая чистая, как мне бы хотелось. Это работало для меня, потому что мое использование не было публично, и SQL-инъекция не вызывала беспокойства. Параметр Aggregate примет любой стандартный sql-агрегат «AVG», «SUM», «MAX» и т. Д. Код также по умолчанию имеет значение MAX как совокупность, это необязательно, но аудитория, изначально построенная для не понимающих опорных точек, и, как правило, используя max в качестве агрегата.
Давайте начнем с кода для создания хранимой процедуры. Этот код должен работать во всех версиях SSMS 2005 и выше, но я не тестировал его в 2005 или 2016 годах, но я не понимаю, почему это не сработает.
create PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
(
@STATIC_COLUMN VARCHAR(255),
@PIVOT_COLUMN VARCHAR(255),
@VALUE_COLUMN VARCHAR(255),
@TABLE VARCHAR(255),
@AGGREGATE VARCHAR(20) = null
)
AS
BEGIN
SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
@SQLSTRING NVARCHAR(MAX),
@PIVOT_SQL_STRING NVARCHAR(MAX),
@TEMPVARCOLUMNS NVARCHAR(MAX),
@TABLESQL NVARCHAR(MAX)
if isnull(@AGGREGATE,'') = ''
begin
SET @AGGREGATE = 'MAX'
end
SET @PIVOT_SQL_STRING = 'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']'' AS VARCHAR(50)) [text()]
FROM '+@TABLE+'
WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
FOR XML PATH(''''), TYPE)
.value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
from '+@TABLE+' ma
ORDER BY ' + @PIVOT_COLUMN + ''
declare @TAB AS TABLE(COL NVARCHAR(MAX) )
INSERT INTO @TAB EXEC SP_EXECUTESQL @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT
SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)
SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')
SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')
INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')
select * from (
SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a
PIVOT
(
'+@AGGREGATE+'('+@VALUE_COLUMN+')
FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
) piv
SELECT * FROM @RETURN_TABLE'
EXEC SP_EXECUTESQL @SQLSTRING
END
Далее мы получим наши данные для примера. Я взял пример данных из принятого ответа с добавлением нескольких элементов данных, которые будут использоваться в этом доказательстве концепции, чтобы показать различные результаты совокупного изменения.
create table temp
(
date datetime,
category varchar(3),
amount money
)
insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('1/1/2012', 'ABC', 2000.00) -- added
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'DEF', 1500.00) -- added
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('2/10/2012', 'DEF', 800.00) -- addded
insert into temp values ('3/1/2012', 'ABC', 1100.00)
Следующие примеры показывают различные примеры выполнения, показывающие разнообразные агрегаты в качестве простого примера. Я не хотел менять столбцы статические, столбцы и значения, чтобы упростить пример. Вы можете просто скопировать и вставить код, чтобы начать с ним общаться.
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','sum'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','max'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','avg'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','min'
Это выполнение возвращает следующие наборы данных соответственно.
@cols
, тогда вы можете удалитьDISTINCT
и использоватьGROUP BY
иORDER BY
, когда получите список@cols
. – Taryn♦ 29 November 2016 в 22:40