Unpivot tableau avec plusieurs colonnes et dynamique des noms de colonne

Je suis en train de unpivot un tableau avec plusieurs lignes et colonnes. Chaque rangée doit être extratced à 2 lignes avec les colonnes spécifiques et les noms de colonnes doivent être renommés et une nouvelle colonne à ajouter en fonction des colonnes sélectionnées!

Je suis y compris avant et après les données de l'échantillon et un script de configuration de données.

 CREATE TABLE #tmpProducts (
ProductId INT,
ProductName nVARCHAR(100),
B2B_GrossRevenue DECIMAL(10,2),
B2B_DirectCost DECIMAL(10,2),
B2B_NetRevenue DECIMAL(10,2),
B2C_GrossRevenue DECIMAL(10,2),
B2C_DirectCost DECIMAL(10,2),
B2C_NetRevenue DECIMAL(10,2)
)
INSERT INTO #tmpProducts SELECT 1, 'Product1',1545.00,406.25,1138.75,195.00,35.10,159.90
INSERT INTO #tmpProducts SELECT 2, 'Product2',902.00,189.00,713.00,3280.00,590.40,2689.60
INSERT INTO #tmpProducts SELECT 3, 'Product3',15665.00,3988.39,11676.61,6247.00,1124.46,5122.54
INSERT INTO #tmpProducts SELECT 4, 'Product4',736.00,196.16,539.84,2395.00,431.10,1963.90
SELECT * FROM #tmpProducts
DROP TABLE #tmpProducts
CREATE TABLE #tmpProducts2 (
ProductId INT,
ProductName nVARCHAR(100),
[Type] nVARCHAR(3),
GrossRevenue DECIMAL(10,2),
DirectCost DECIMAL(10,2),
NetRevenue DECIMAL(10,2)
)
INSERT INTO #tmpProducts2 SELECT 1, 'Product1','B2B',1545.00,406.25,1138.75
INSERT INTO #tmpProducts2 SELECT 1, 'Product1','B2C',195.00,35.10,159.90
INSERT INTO #tmpProducts2 SELECT 2, 'Product2','B2B',902.00,189.00,713.00
INSERT INTO #tmpProducts2 SELECT 2, 'Product2','B2C',3280.00,590.40,2689.60
INSERT INTO #tmpProducts2 SELECT 3, 'Product3','B2B',15665.00,3988.39,11676.61
INSERT INTO #tmpProducts2 SELECT 3, 'Product3','B2C',6247.00,1124.46,5122.54
INSERT INTO #tmpProducts2 SELECT 4, 'Product4','B2B',736.00,196.16,539.84
INSERT INTO #tmpProducts2 SELECT 4, 'Product4','B2C',2395.00,431.10,1963.90
SELECT * FROM #tmpProducts2
DROP TABLE #tmpProducts2

J'ai essayé cela, mais je ne peux pas obtenir au-delà de la deuxième colonne et je ne sais pas comment ajouter une nouvelle colonne avec un texte spécifique, (probablement le sql dynamique, mais en essayant d'éviter si possible)

C'est là le début de ma tentative, toute aide serait grandement appréciée.

SELECT  ProductId, ProductName,GrossRevenue
FROM    (
SELECT ProductId, ProductName, B2B_GrossRevenue,B2C_GrossRevenue FROM #tmpProducts
) as t
UNPIVOT ( GrossRevenue for test IN (B2B_GrossRevenue,B2C_GrossRevenue)) AS unpvt

OriginalL'auteur stevenmahony | 2012-08-09

Vous juste gardez unpivoting

    SELECT  ProductId,ProductName, Substring(col1,1,3) as type,  GrossRevenue, DirectCost, NetRevenue
FROM    ( 
SELECT * FROM #tmpProducts 
) as t 
UNPIVOT ( GrossRevenue for col1 IN (B2B_GrossRevenue,B2C_GrossRevenue)) AS unpvt 
unpivot ( DirectCost for col2 in (B2b_DirectCost, B2c_DirectCost)) up2
unpivot ( NetRevenue for col3 in (B2b_NetRevenue, B2c_NetRevenue)) up3
where SUBSTRING(col1,1,3)=SUBSTRING(col2,1,3)
and  SUBSTRING(col1,1,3)=SUBSTRING(col3,1,3)

et rejoindre le col des colonnes de filtrer les inadéquations

Qui a fait le travail parfaitement. Je vous remercie pour votre aide. Premier temps d'accepter une réponse sur statckoverflow. Dois-je cliquer pour accepter la réponse?

OriginalL'auteur podiluska

Ici est une façon de le faire à l'aide de SQL Dynamique. Cela vous permettra d'obtenir toutes les colonnes lors de l'exécution. Ensuite, vous n'aurez pas à modifier la requête si vous avez des modifications de données. Ce n'est à la fois un UNPIVOT et PIVOT:

DECLARE @colsUnPivot AS NVARCHAR(MAX),
@colsPivot AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
SET @colsUnPivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('tmpProducts') and
C.name like 'B2%'
for xml path('')), 1, 1, '')
SET @colsPivot = stuff((select DISTINCT ','+quotename(right((C.name), len(C.name)-4))
from sys.columns as C
where C.object_id = object_id('tmpProducts') and
C.name like 'B2%'
for xml path('')), 1, 1, '')
set @query 
= ' SELECT ProductId, ProductName, [type], ' + @colsPivot +'
FROM
(
SELECT ProductId, ProductName, substring(field, 1, 3) [type]
,  value, right((field), len(field)-4) as col
from tmpProducts
unpivot 
(
value
for field in (' + @colsUnPivot + ')
) unpvt 
) x
PIVOT
(
sum(value)
FOR col IN (' + @colsPivot +')
)p'
execute(@query)

Voir SQL jouer avec la Démo

Apprécier la réponse. Podiluska de la réponse qu'a fait le travail.

OriginalL'auteur Taryn

Si vous avez plusieurs groupes que vous êtes à la recherche de pivoter autour de vous pouvez aussi faire quelque chose comme ceci:

Select *
from table
unpivot include nulls ( (test_name, test_date) for col_nm in ( (test1, date1) as 'test_val1'
,(test2, date2) as 'test_val2'
,(test3, date3) as 'test_val3'
)
)

OriginalL'auteur D. J.

Vous devez vous connecter pour publier un commentaire.