Comment convertir deux lists en masortingce d'adjacence SQL Server T-SQL?

J'ai une table dans SQL Server 2005 qui contient deux colonnes de fruits et de colors comme indiqué ci-dessous

Fruit Colour Apple Red Orange Red Berry Green PineApple Green 

Maintenant, je veux le convertir en une requête de masortingce d'adjacence comme indiqué ci-dessous

  Red Green Apple 1 0 Orange 1 0 Berry 0 1 PineApple 0 1 

ce que je veux faire est de donner 1 si le fruit et la couleur sont là je veux donner un 0

Il était difficile d'éviter ces valeurs nulles dans le pivot.

 declare @t table (fruit varchar(10), colour varchar(10)) insert @t select 'Apple', 'Red' union all select 'Orange', 'Red' union all select 'Berry', 'Green' union all select 'PineApple', 'Green' select * from ( select a.fruit, b.colour, case when c.fruit is null then 0 else 1 end found from (select distinct fruit, colour from @t) a cross join (select distinct colour from @t) b left outer join (select distinct fruit, colour from @t) c on a.fruit = c.fruit and b.colour = c.colour) d PIVOT (max(found) FOR colour in([red],[green]) )AS p order by 3, 1 

Sortie

 fruit red green ---------- ----------- ----------- Apple 1 0 Orange 1 0 Berry 0 1 PineApple 0 1 

Voici un exemple:

 CREATE TABLE #Fruits (name varchar(10), color varchar(10)) GO INSERT #Fruits VALUES ('Apple', 'Red') INSERT #Fruits VALUES ('Orange', 'Red') INSERT #Fruits VALUES ('Berry', 'Green') INSERT #Fruits VALUES ('PineApple', 'Green') GO DECLARE @select_query nvarchar(4000); DECLARE table_cursor CURSOR FOR SELECT DISTINCT color FROM #Fruits; DECLARE @color varchar(10); SET @select_query = N'SELECT name' OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @color; WHILE @@FETCH_STATUS = 0 BEGIN SET @select_query = @select_query + N', CASE WHEN color = ''' + @color + N''' THEN 1 ELSE 0 END AS ' + @color; FETCH NEXT FROM table_cursor INTO @color; END CLOSE table_cursor; DEALLOCATE table_cursor; SET @select_query = @select_query + N' FROM #Fruits'; EXEC sp_executesql @select_query 
  1. Trouver des colors distinctes
  2. Créer une déclaration SELECT
  3. Exécutez-le via sp_executesql

essayez le Pivot dynamic. (J'ai supposé que le nom de votre table est t1)

comme ça:

 declare @sql nvarchar(max) set @sql = '' select @sql = @sql +'['+Colour +'],' from (select distinct colour from t1) t2 set @sql = subssortingng(@sql,1,len(@sql) - 1) set @sql = ' select * from( select Fruit, Colour, 1 as CheckMark from t1 ) as t2 pivot ( SUM(CheckMark) for Colour in ('+@sql+') ) as pivotTable' execute (@sql) 

cette requête renvoie NULL au lieu de 0.

j'espère avoir aidé

 SELECT Fruit, CASE WHEN Colour = 'Red' THEN 1 ELSE 0 END AS Red CASE WHEN Colour = 'Green' THEN 1 ELSE 0 END AS Green FROM [TABLE_NAME] 
 CREATE TABLE #t(fruit VARCHAR(10), colour VARCHAR(10)) INSERT #t SELECT 'Apple', 'Red' UNION ALL SELECT 'Orange', 'Red' UNION ALL SELECT 'Berry', 'Green' UNION ALL SELECT 'PineApple', 'Green' DECLARE @pivot_columns VARCHAR(2000) SET @pivot_columns = '' SELECT @pivot_columns = @pivot_columns + '[' + colour + '],' FROM (SELECT DISTINCT colour FROM #t)t SET @pivot_columns = LEFT(@pivot_columns, LEN(@pivot_columns) - 1) PRINT @pivot_columns DECLARE @sql VARCHAR(MAX) SET @sql = ' SELECT fruit, ' + @pivot_columns + ' FROM ( SELECT fruit, colour FROM #t ) AS SourceTable PIVOT ( COUNT(colour) FOR Colour IN (' + @pivot_columns + ') ) AS PivotTable' PRINT @sql EXEC (@sql) DROP TABLE #t 

Il est possible de garder la nature dynamic de la réponse de Maziar Taheri et de se débarrasser des null:

 if object_id('tempdb..#t') is not null drop table #t create table #t (fruit varchar(10), colour varchar(10)) insert #t select 'Apple', 'Red' union all select 'Apple', 'Green' union all select 'Apple', 'Yellow' union all select 'Apple', 'Yellow' union all select 'Orange', 'Red' union all select 'Orange', 'Yellow' union all select 'Berry', 'Green' union all select 'Berry', 'Red' union all select 'Berry', 'Black' union all select 'PineApple', 'Green' declare @sql nvarchar(max), @colours nvarchar(max) select @sql = '', @colours = '' select @colours = @colours +'['+Colour +'],', @sql = @sql +'isnull(['+Colour+'],0) '+Colour+',' from (select distinct colour from #t) t2 set @colours = subssortingng(@colours,1,len(@colours) - 1) set @sql = subssortingng(@sql,1,len(@sql) - 1) select @sql = ' select Fruit,'+@sql +' from ( select Fruit, Colour, 1 as CheckMark from #t ) as t2 pivot ( SUM(CheckMark) for Colour in ('+@colours+') ) as pivotTable' execute (@sql)