SQL SERVER 2008 Pivot

Question

SQL SERVER 2008 Pivot

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

NY, NY - so good they named it twice. Ok, are you entirely happy with these results? Is the data exactly what you would expect to see?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

datelligence Say Hey Kid Points: 672 More actions · Answer 2

Yes, this is satisfactory. What I need is instead of manually defining the variables in the query, it must be defined dinamically by the selection made by user in a web interface.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

datelligence (1/21/2010)
Yes, this is satisfactory. What I need is instead of manually defining the variables in the query, it must be defined dinamically by the selection made by user in a web interface.

-- Check that the code executes ok

DECLARE @PivotTableSQL VARCHAR(MAX), @PivotCliPadres VARCHAR(200)

SET @PivotCliPadres = '[130], [1652], [489]'

-- no CliPadres string

SET @PivotTableSQL =

'SELECT *

FROM (

SELECT

codigo_item,

item,

round(net_price,1) as net_price,

id_CliPadre,

id_stock

FROM dbo.sipFTdataStage f

INNER JOIN dbo.sipDimensionPos p ON f.id_cliente = p.id_cliente

) tabla

PIVOT (SUM(net_price) FOR id_CliPadre IN ([130], [1652], [489])) pivotable'

PRINT @PivotTableSQL

-- with CliPadres string

SET @PivotTableSQL =

'SELECT *

FROM (

SELECT

codigo_item,

item,

round(net_price,1) as net_price,

id_CliPadre,

id_stock

FROM dbo.sipFTdataStage f

INNER JOIN dbo.sipDimensionPos p ON f.id_cliente = p.id_cliente

) tabla

PIVOT (SUM(net_price) FOR id_CliPadre IN (' + @PivotCliPadres + ')) pivotable'

PRINT @PivotTableSQL

-- ensure it runs

EXECUTE(@PivotTableSQL)

-- Ensure that @PivotCliPadres is correctly populated

SELECT @PivotCliPadres

SELECT @PivotCliPadres =

COALESCE(

@PivotCliPadres + ',[' + cast(id_CliPadre as varchar) + ']',

'[' + cast(id_CliPadre as varchar)+ ']'

)

FROM dbo.sipDimensionPos

SELECT @PivotCliPadres

-- check if it runs with table-derived CliPadres string

SET @PivotTableSQL =

'SELECT *

FROM (

SELECT

codigo_item,

item,

round(net_price,1) as net_price,

id_CliPadre,

id_stock

FROM dbo.sipFTdataStage f

INNER JOIN dbo.sipDimensionPos p ON f.id_cliente = p.id_cliente

) tabla

PIVOT (SUM(net_price) FOR id_CliPadre IN (' + @PivotCliPadres + ')) pivotable'

PRINT @PivotTableSQL

EXECUTE(@PivotTableSQL)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

datelligence Say Hey Kid Points: 672 More actions · Answer 4

Hi Chris, you´re great, the solution is close. When executed what you suggest I receive the following:

-- Check that the code executes ok:

Command(s) completed successfully.

-- no CliPadres string

Query Executed successfully. --Don´t show me a result pane.

-- with CliPadres string

Query Executed successfully. --Don´t show me a result pane.

-- ensure it runs

Query Executed successfully. --Don´t show me a result pane.

-- Ensure that @PivotCliPadres is correctly populated

(1 row(s) affected) - Shows me a datagrid with the correct values, Clipadres.

-- check if it runs with table-derived CliPadres string

Query completed with errors.

The message is:

--SELECT *

FROM (

SELECT

codigo_item,

item,

round(net_price,1) as net_price,

id_CliPadre,

id_stock

FROM dbo.sipFTdataStage f

INNER JOIN dbo.sipDimensionPos p ON f.id_cliente = p.id_cliente

) tabla

PIVOT (SUM(net_price) FOR id_CliPadre IN ([155],[110],[110],[110],[110],[155],[155],[155],[110],[115],[624],[1652],[135],[110],[1652],[115],[155],[155],[155],[155],[130],[130],[1652],[1652],[1652],[135],[155],[1652],[155],[155],[155],[155],[1)) pivotable

Msg 105, Level 15, State 1, Line 12

Unclosed quotation mark after the character string '1)) pivotable'.

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near '1)) pivotable'.

--

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

That's because @PivotCliPadres variable is too short, the value is truncated.

However, @PivotCliPadres contains duplicate values, even if PIVOT can deal with this, it's almost certainly not what you want.

Can I suggest we change tack to deal with this before switching back to the pivot?

What I suggest you do is set up a sample table so we can work on the statement

SELECT @PivotCliPadres =

COALESCE(

@PivotCliPadres + ',[' + cast(id_CliPadre as varchar) + ']',

'[' + cast(id_CliPadre as varchar)+ ']'

)

FROM dbo.sipDimensionPos

Start with CREATE TABLE #sipDimensionPos (a local temporary table), with structure the same as dbo.sipDimensionPos. Populate it with say 20 sample rows, using real data if possible. There are articles to show you how to do this...such as here[/url].

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

datelligence Say Hey Kid Points: 672 More actions · Answer 6

Well Chris, about the variable size, yes I tested it and it works, with 1000 bits size.

Now let´s go with second point:

--===== Create test table #sipDimensionPos

SELECT 'SELECT '

+ QUOTENAME(id_pos,'''')+','

+ QUOTENAME(id_cliente,'''')+','

+ QUOTENAME(razonSocial_cliente,'''')+','

+ QUOTENAME(id_CliPadre,'''')+','

+ QUOTENAME(id_canal,'''')+','

+ QUOTENAME(id_sucursal,'''')+','

+ QUOTENAME(establecimiento,'''')+','

+ QUOTENAME(nombre_Localidad,'''')+','

+ QUOTENAME(ponderado_gondola,'''')+','

+ QUOTENAME(id_formato,'''')+','

+ QUOTENAME(id_sucNielsen,'''')+','

+ QUOTENAME(Periodicidad,'''')+','

+ QUOTENAME(ttal,'''')+','

+ QUOTENAME(numero_Registro,'''')

+ ' UNION ALL'

FROM dbo.sipDimensionPos

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #sipDimensionPos ON

--===== Insert the test data into the test table

SELECT '1','21004','Almacenes Exito S.A.','155','95','1','Exito San Fernando # 051','Cali (Valle)','5793.100','1','6','Semanal','1','1' UNION ALL

SELECT '2','92011','Almacenes La 14 S.A.','110','95','1','La 14 Av Sexta # 5','Cali (Valle)','10010.480','9999','6','Semanal','2','2' UNION ALL

SELECT '3','92097','Almacenes La 14 S.A.','110','95','1','La 14 Buenaventura # 15','Buenaventura (Valle)','8342.060','9999','6','Semanal','3','3' UNION ALL

SELECT '4','92103','Almacenes La 14 S.A.','110','95','1','La 14 Pereira # 23','Pereira (Risaralda)','4022.980','9999','5','Semanal','4','4' UNION ALL

SELECT '5','92875','Almacenes La 14 S.A.','110','95','1','La 14 Calima # 8','Cali (Valle)','12012.570','9999','6','Semanal','5','5' UNION ALL

SELECT '6','100399','Almacenes Exito S.A.','155','95','1','Exito Unicentro Cali','Cali (Valle)','4022.980','1','6','Semanal','6','6' UNION ALL

SELECT '7','100879','Almacenes Exito S.A.','155','95','1','Almacenes Exito Buenaventura','Buenaventura (Valle)','261.000','1','6','Semanal','7','7' UNION ALL

SELECT '8','128199','Almacenes Exito S.A.','155','95','1','Exito Pasto','Pasto (Nariño)','2793.740','1','6','Semanal','8','8' UNION ALL

SELECT '9','131256','Almacenes La 14 S.A.','110','95','1','La 14 Tulua # 27','Tulua (Valle)','2328.110','9999','6','Semanal','9','9' UNION ALL

SELECT '10','135275','Caja Comp. Fliar. Comfandi','115','95','1','Comfandi Decepaz','Cali (Valle)','1616.000','9999','6','Semanal','10','10' UNION ALL

SELECT '11','138265','Alvarado Parra Carlos Jose','624','94','1','Supermercado Mercacentro # 4','Ibague (Tolima)','4827.580','9999','5','Quincenal','11','11' UNION ALL

SELECT '12','141535','Grandes Superf. Decolombia','1652','95','1','Carrefour Pereira','Pereira (Risaralda)','5793.100','9999','5','Semanal','12','12' UNION ALL

SELECT '13','147573','Olimpica Sao 380 Neiva','135','95','1','Olimpica Neiva # 380','Neiva (Huila)','1347.290','1','5','Semanal','13','13' UNION ALL

SELECT '14','148109','Almacenes La 14 S.A.','110','95','1','La 14 Pasoancho # 33','Cali (Valle)','10010.480','9999','6','Semanal','14','14' UNION ALL

SELECT '15','155687','Grandes Superficies De Colombi','1652','95','1','Carrefour Buga','Buga (Valle)','1616.740','9999','6','Semanal','15','15' UNION ALL

SELECT '16','156071','Caja Comp. Fliar. Comfandi','115','95','1','Comfandi Morichal','Cali (Valle)','1347.290','9999','6','Semanal','16','16' UNION ALL

SELECT '17','158851','Almacenes Exito S.A.','155','95','2','Exito Calle 80','Bogota D.C.','8342.060','1','4','Semanal','47','1' UNION ALL

SELECT '18','158868','Almacenes Exito S.A.','155','95','2','Exito Norte # 092','Bogota D.C.','6951.720','1','4','Semanal','48','2' UNION ALL

SELECT '19','158875','Almacenes Exito S.A.','155','95','2','Exito Americas # 084','Bogota D.C.','8342.060','1','4','Semanal','49','3' UNION ALL

SELECT '20','158907','Almacenes Exito S.A.','155','95','1','Exito Neiva # 157','Neiva (Huila)','4827.580','1','5','Semanal','17','17' UNION ALL

SELECT '21','161356','Almacenes Yep S.A.','130','94','2','Almacen Yep Villavicencio','Villavicencio (Meta)','541.440','9999','3','Semanal','50','4' UNION ALL

SELECT '22','161509','Almacenes Yep S.A.','130','94','1','Almacenes Yep Neiva','Neiva (Huila)','1940.090','9999','5','Quincenal','18','18' UNION ALL

SELECT '23','164246','Grandes Superficies De Colombi','1652','95','1','Carrefour Chipichape','Cali (Valle)','3352.490','9999','6','Semanal','19','19' UNION ALL

SELECT '24','171391','Grandes Superfices De Colombia','1652','95','1','Carrefour Ibague','Ibague (Tolima)','4022.980','9999','5','Semanal','20','20' UNION ALL

SELECT '25','171823','Grandes Superficies De Colombi','1652','95','1','Carrefour Valle Del Lili','Cali (Valle)','4827.580','9999','6','Semanal','21','21' UNION ALL

SELECT '26','173675','Olimpica S.a.','135','95','1','Supermercado Olimpica Sto 359','Armenia (Quindio)','451.200','2','5','Semanal','22','22' UNION ALL

SELECT '27','174519','Almacenes Exito S.A.','155','95','1','Ley San Cancio Manizales # 420','Manizales (Caldas)','779.680','2','5','Semanal','23','23' UNION ALL

SELECT '28','175045','Grandes Superficies De Colombi','1652','95','1','Carrefour Neiva','Neiva (Huila)','4022.980','9999','5','Semanal','24','24' UNION ALL

SELECT '29','176973','Almacenes Exito S.A.','155','95','1','Ley Pereira Centro # 424','Pereira (Risaralda)','935.610','2','5','Semanal','25','25' UNION ALL

SELECT '30','177700','Almacenes Exito S.A.','155','95','1','Exito La Flora # 054','Cali (Valle)','4022.980','1','6','Semanal','26','26' UNION ALL

SELECT '31','189690','Almacenes Exito S.A.','155','95','1','Exito Ibague # 156','Ibague (Tolima)','4022.980','1','5','Semanal','27','27' UNION ALL

SELECT '32','192090','Almacenes Exito S.A.','155','95','1','Exito Tulua','Tulua (Valle)','1122.740','9999','6','Semanal','28','28' UNION ALL

SELECT '33','192579','Almacenes Exito S.A.','155','95','1','Ley Armenia # 628','Armenia (Quindio)','649.730','2','5','Semanal','29','29' UNION ALL

SELECT '34','193358','Comerc.Giraldo Y Gomez Cia S A','1676','94','1','Superinter Autoservicio Siloe','Cali (Valle)','2328.110','9999','6','Quincenal','30','30' UNION ALL

SELECT '35','198022','Colsubsidio','233','95','2','Supermercado Calle 26','Bogota D.C.','5793.100','9999','4','Semanal','51','5' UNION ALL

SELECT '36','198046','Colsubsidio','233','95','2','Supermercado Calle 63','Bogota D.C.','1940.090','9999','4','Semanal','52','6' UNION ALL

SELECT '37','198350','Caja Comp. Fliar. Cafam','120','95','2','Supermercado Nuevo Kennedy','Bogota D.C.','3352.490','9999','4','Semanal','53','7' UNION ALL

SELECT '38','198351','Almacenes Exito S.A.','155','95','1','Exito Pereira #063','Pereira (Risaralda)','4028.980','1','5','Semanal','31','31' UNION ALL

SELECT '39','198367','Caja Comp. Fliar. Cafam','120','95','2','Supermercado Floresta','Bogota D.C.','8342.060','9999','4','Semanal','54','8' UNION ALL

SELECT '40','198451','Grandes Superficies De Colombi','1652','95','1','Carrefour Popayan','Popayan (Cauca)','1122.740','9999','6','Semanal','32','32' UNION ALL

SELECT '41','198664','Caja Comp. Fliar. Cafam','120','95','2','Supermecado 20 De Julio','Bogota D.C.','1940.090','9999','4','Semanal','55','9' UNION ALL

SELECT '42','202020','Almacenes Exito S.A.','155','95','4','Exito Colombia # 031','Medellin (Antioquia)','10010.480','1','2','Semanal','135','1' UNION ALL

SELECT '43','202037','Almacenes Exito S.A.','155','95','4','Exito Envigado # 035','Envigado (Antioquia)','10010.480','1','2','Semanal','136','2' UNION ALL

SELECT '44','202051','Almacenes Exito S.A.','155','95','4','Exito Poblado # 033','Medellin (Antioquia)','12012.570','1','2','Semanal','137','3' UNION ALL

SELECT '45','202068','Almacenes Exito S.A.','155','95','4','Exito San Antonio # 039','Medellin (Antioquia)','10010.480','1','2','Semanal','138','4' UNION ALL

SELECT '46','205617','Grandes Superficies De Colombi','1652','95','2','Carrefour Carrera 30','Bogota D.C.','10010.480','9999','4','Semanal','56','10' UNION ALL

SELECT '47','206161','Almacenes Exito S.A.','155','95','2','Carulla Castilla','Bogota D.C.','1940.090','8','4','Semanal','57','11' UNION ALL

SELECT '48','206246','Almacenes Exito S.A.','155','95','2','Carulla Country','Bogota D.C.','5793.100','8','4','Semanal','58','12' UNION ALL

SELECT '49','206284','Almacenes Exito S.A.','155','95','2','Carulla Galerias','Bogota D.C.','4022.980','8','4','Semanal','59','13' UNION ALL

SELECT '50','206420','Almacenes Exito S.A.','155','95','2','Carulla Pablo VI','Bogota D.C.','2793.740','8','4','Semanal','60','14' UNION ALL

SELECT '51','209520','Colsubsidio','233','95','2','Superm. Unicentro Occidente','Bogota D.C.','4022.980','9999','4','Semanal','61','15' UNION ALL

SELECT '52','211743','Colsubsidio','233','95','2','COLSUBSIDIO Nueva Zelandia','Bogota D.C.','1616.740','9999','4','Semanal','62','16' UNION ALL

SELECT '53','214143','Caja Comp. Fliar. Cafam','120','95','2','Minimercado Express Cll 85 Del','Bogota D.C.','1347.000','9999','4','Semanal','63','17' UNION ALL

SELECT '54','214436','Almacenes Exito S.A.','155','95','2','Exito Gran Estacion','Bogota D.C.','4022.980','1','4','Semanal','64','18' UNION ALL

SELECT '55','220149','Almacenes Exito S.A.','155','95','2','Exito Colina # 088','Bogota D.C.','8342.060','1','4','Semanal','65','19' UNION ALL

SELECT '56','225428','Almacenes Exito S.A.','155','95','2','Exito Chapinero','Bogota D.C.','8342.060','1','4','Semanal','66','20' UNION ALL

SELECT '57','226990','Olimpica S.A.','135','95','2','Supertienda Olimpica Unisur','Soacha (Cundinamarca)','1347.290','2','4','Semanal','67','21' UNION ALL

SELECT '58','228578','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Prado Veranieg','Bogota D.C.','1940.090','7','4','Semanal','68','22' UNION ALL

SELECT '59','229666','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Avenida Rojas','Bogota D.C.','935.610','7','4','Semanal','69','23' UNION ALL

SELECT '60','230680','Olimpica S.A.','135','95','2','Sao Portal De La 80 #432','Bogota D.C.','3352.490','1','4','Semanal','70','24' UNION ALL

SELECT '61','234548','Grandes Superficies De Colombi','1652','95','2','Carrefour Villavicencio','Villavicencio (Meta)','3352.490','9999','3','Semanal','71','25' UNION ALL

SELECT '62','234664','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Primavera','Bogota D.C.','1616.740','7','4','Semanal','72','26' UNION ALL

SELECT '63','238031','Caja Comp. Fliar. Del Valle D','115','95','1','Comfandi Guadalupe','Cali (Valle)','4022.980','9999','6','Semanal','33','33' UNION ALL

SELECT '64','238176','El Arrozal Y Cia S. En C.','1152','94','2','El Arrozal Estrada','Bogota D.C.','1616.740','9999','4','Quincenal','73','27' UNION ALL

SELECT '65','239391','Almacenes Exito S.A.','155','95','2','Exito Country # 081','Bogota D.C.','12012.570','1','4','Semanal','74','28' UNION ALL

SELECT '66','243103','Almacenes Exito S.A.','155','95','2','Exito Unicentro Bogota # 303','Bogota D.C.','8342.060','1','4','Semanal','75','29' UNION ALL

SELECT '67','249308','Almacenes Exito S.A.','155','95','2','Exito Villa Mayor # 083','Bogota D.C.','6951.720','1','4','Semanal','76','30' UNION ALL

SELECT '68','250011','Grandes Superficies De Colombi','1652','95','2','Carrefour Autopista Sur','Bogota D.C.','5793.100','9999','4','Semanal','77','31' UNION ALL

SELECT '69','251789','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Calle 80','Bogota D.C.','1347.290','7','4','Semanal','78','32' UNION ALL

SELECT '70','252807','Olimpica S.A.','135','95','2','Supertiendaolimpicacolinaca418','Bogota D.C.','3352.490','2','4','Semanal','79','33' UNION ALL

SELECT '71','255057','Grandes Superficies De Colombi','1652','95','2','Carrefour Santa Ana','Bogota D.C.','10010.480','9999','4','Semanal','80','34' UNION ALL

SELECT '72','257475','Almacenes Exito S.A.','155','95','2','Carulla Pepe Sierra','Bogota D.C.','8342.060','8','4','Semanal','81','35' UNION ALL

SELECT '73','259295','Colombiana De Comercio S.A.','489','94','2','Alkosto Villavicencio','Villavicencio (Meta)','3352.490','9999','3','Quincenal','82','36' UNION ALL

SELECT '74','259707','Colombiana De Comercio S.A.','489','94','2','Alkosto Avenida 68','Bogota D.C.','6951.720','9999','4','Quincenal','83','37' UNION ALL

SELECT '75','260060','Colombiana De Comercio S.A.','489','94','2','Alkosto Carrera 30','Bogota D.C.','3352.490','9999','4','Quincenal','84','38' UNION ALL

SELECT '76','261536','Almacenes Exito S.A.','155','95','2','Carulla Calle 140','Bogota D.C.','6951.720','1','4','Semanal','85','39' UNION ALL

SELECT '77','264054','Grandes Superficies De Colombi','1652','95','2','Carrefour 170','Bogota D.C.','10010.480','9999','4','Semanal','86','40' UNION ALL

SELECT '78','277062','El Arrozal y Cia S. En C.','1152','94','2','Lider San Pablo','Bogota D.C.','2328.110','9999','4','Quincenal','87','41' UNION ALL

SELECT '79','277555','Grandes Superficies De Colombi','1652','95','2','Carrefour Suba','Bogota D.C.','5793.100','9999','4','Semanal','88','42' UNION ALL

SELECT '80','278840','Grandes Superficies De Colombi','1652','95','2','Carrefour Calle 80','Bogota D.C.','12012.570','9999','4','Semanal','89','43' UNION ALL

SELECT '81','284858','Almacenes Exito S.A.','155','95','3','Exito 366 Murillo','Barranquilla (Atlantico)','1940.090','1','1','Semanal','100','1' UNION ALL

SELECT '82','293046','Coop. De Consumo De Antioquia','240','94','4','Consumo America','Medellin (Antioquia)','60.720','9999','2','Quincenal','139','5' UNION ALL

SELECT '83','295000','Almacenes Exito S.A.','155','95','2','Bodega Surtimax San Blas','Bogota D.C.','1616.740','7','4','Semanal','90','44' UNION ALL

SELECT '84','295352','Almacenes Exito S.A.','155','95','2','Exito Suba','Bogota D.C.','4022.980','1','4','Semanal','91','45' UNION ALL

SELECT '85','296206','Grandes Superficies De Colombi','1652','95','2','Carrefour Hayuelos','Bogota D.C.','10010.480','9999','4','Semanal','92','46' UNION ALL

SELECT '86','301790','Almacenes Exito S.A.','155','95','3','Exito 369 Panorama','Barranquilla (Atlantico)','1940.090','1','1','Semanal','101','2' UNION ALL

SELECT '87','312570','Grandes Superficies de Colombi','1652','95','3','Carrefour C/gena. Caribe Plaza','Cartagena (Bolivar)','4022.980','9999','1','Semanal','102','3' UNION ALL

SELECT '88','315059','Almacenes Exito S.A.','155','95','3','Exito 370 Castellana Cartagena','Cartagena (Bolivar)','3352.490','1','1','Semanal','103','4' UNION ALL

SELECT '89','317058','Almacenes Exito S.A.','155','95','3','Exito 367 San Diego C/gena','Cartagena (Bolivar)','1616.740','1','1','Semanal','104','5' UNION ALL

SELECT '90','318915','Almacenes Exito S.A.','155','95','3','Exito 368 San Francisco','Barranquilla (Atlantico)','2328.110','1','1','Semanal','105','6' UNION ALL

SELECT '91','334300','Grandes Superficies de Colombi','1652','95','3','Carrefour Americano','Barranquilla (Atlantico)','779.680','9999','1','Semanal','106','7' UNION ALL

SELECT '92','336244','Grandes Superficies de Colombi','1652','95','3','Carrefour Guatapuri','Valledupar (Cesar)','4022.000','9999','1','Semanal','107','8' UNION ALL

SELECT '93','346594','Almacenes Exito S.A.','155','95','3','Exito San Blas','Barranquilla (Atlantico)','935.610','1','1','Semanal','108','9' UNION ALL

SELECT '94','347500','Olimpica S.A.','135','95','1','Olimpica Central','Popayan (Cauca)','779.680','1','6','Semanal','34','34' UNION ALL

SELECT '95','353699','Almacenes Exito S.A.','155','95','3','Exito CentroSanta Marta','Santa Marta (Magdalena)','1616.740','1','1','Semanal','109','10' UNION ALL

SELECT '96','357495','Almacenes Exito S.A.','155','95','3','Exito Barranquilla','Barranquilla (Atlantico)','4827.580','1','1','Semanal','110','11' UNION ALL

SELECT '97','366084','Almacenes Exito S.A.','155','95','3','Ley Valledupar # 266','Valledupar (Cesar)','1940.090','1','1','Semanal','111','12' UNION ALL

SELECT '98','372158','Almacenes Exito S.A.','155','95','3','Exito Sincelejo','Sincelejo (Sucre)','2328.110','1','1','Semanal','112','13' UNION ALL

SELECT '99','375264','Grandes Superficies de Colombi','1652','95','3','Carrefour Monteria','Monteria (Cordoba)','1940.090','9999','1','Semanal','113','14' UNION ALL

SELECT '100','379085','Almacenes Exito S.A.','155','95','3','Exito 363 Buenavista St. Marta','Santa Marta (Magdalena)','1616.740','1','1','Semanal','114','15' UNION ALL

SELECT '101','380523','Almacenes Exito S.A.','155','95','3','Exito Barranquilla Sur','Barranquilla (Atlantico)','1940.090','1','1','Semanal','115','16' UNION ALL

SELECT '102','387238','Almacenes Exito S.A.','155','95','3','Exito Alamedas del Sinu 357','Monteria (Cordoba)','2793.740','1','1','Semanal','116','17' UNION ALL

SELECT '103','389907','Almacenes Exito S.A.','155','95','3','Exito Ejecutivos # 257','Cartagena (Bolivar)','1347.290','1','1','Semanal','117','18' UNION ALL

SELECT '104','391635','Olimpica S.A.','135','95','3','Sao # 031 Hipodromo','Soledad (Atlantico)','2793.740','1','1','Semanal','118','19' UNION ALL

SELECT '105','391680','Olimpica S.A.','135','95','3','Olimpica Rodadero # 202','Santa Marta (Magdalena)','1347.290','2','1','Semanal','119','20' UNION ALL

SELECT '106','391727','Olimpica S.A.','135','95','3','Sao Santa Marta # 203','Santa Marta (Magdalena)','2328.110','1','1','Semanal','120','21' UNION ALL

SELECT '107','391925','Olimpica S.A.','135','95','3','Olimpica # 514','Barranquilla (Atlantico)','2328.110','3','1','Semanal','121','22' UNION ALL

SELECT '108','391956','Olimpica S.A.','135','95','3','Olimpica Pie De Popa # 107','Cartagena (Bolivar)','1616.740','2','1','Semanal','122','23' UNION ALL

SELECT '109','392096','Olimpica S.A.','135','95','3','Sao Plazuela # 105','Cartagena (Bolivar)','3352.490','1','1','Semanal','123','24' UNION ALL

SELECT '110','392327','Grandes Superficies De Colombi','1652','95','3','Carrefour Barranquilla','Barranquilla (Atlantico)','4827.580','9999','1','Semanal','124','25' UNION ALL

SELECT '111','392340','Grandes Superficiesde Colombia','1652','95','3','Carrefour Santa Marta','Santa Marta (Magdalena)','4022.980','9999','1','Semanal','125','26' UNION ALL

SELECT '112','393751','Nuevo Mercadefam S.A.','487','95','6','Mercadefam Cabecera','Bucaramanga (Santander)','10010.480','9999','3','Semanal','148','1' UNION ALL

SELECT '113','396707','Almacenes Exito S.A.','155','95','3','Exito 354 las Flores V/dupar','Valledupar (Cesar)','2328.110','1','1','Semanal','126','27' UNION ALL

SELECT '114','400177','Almacenes Exito S.A.','155','95','4','Exito Bello # 030','Bello (Antioquia)','5793.100','1','2','Semanal','140','6' UNION ALL

SELECT '115','432420','Grandes Superficies De Colombi','1652','95','4','Carrefour Cra 65','Medellin (Antioquia)','8342.060','9999','2','Semanal','141','7' UNION ALL

SELECT '116','451334','Almacenes Exito S.A.','155','95','4','Carulla Belen','Medellin (Antioquia)','1347.290','8','2','Semanal','142','8' UNION ALL

SELECT '117','452088','Almacenes Exito S.A.','155','95','4','Carulla America # 11','Medellin (Antioquia)','1940.090','8','2','Semanal','143','9' UNION ALL

SELECT '118','453910','Coop. De Consumo De Antioquia','240','94','4','Consumo Envigado','Envigado (Antioquia)','104.930','9999','2','Quincenal','144','10' UNION ALL

SELECT '119','463157','Almacenes Exito S.A.','155','95','4','Carulla San Ignacio','Medellin (Antioquia)','1940.090','8','2','Semanal','145','11' UNION ALL

SELECT '120','488584','Almacenes Exito S.A.','155','95','4','Exito Laureles # 037','Medellin (Antioquia)','10010.480','1','2','Semanal','146','12' UNION ALL

SELECT '121','604172','Almacenes Exito S.A.','155','95','6','Exito San Mateo','Cucuta (Norte De Santander)','1940.090','1','3','Semanal','149','2' UNION ALL

SELECT '122','604481','Grandes Superficies de Colombi','1652','95','6','Carefour Floridablanca','Floridablanca (Santander)','1347.000','9999','3','Semanal','150','3' UNION ALL

SELECT '123','610259','Grandes Superficies De Colombi','1652','95','2','Carrefour Tunja','Tunja (Boyaca)','1940.090','9999','3','Semanal','93','47' UNION ALL

SELECT '124','617172','Grandes Superficies de Colombi','1652','95','6','Carrefour Giron','Giron (Santander)','1177.000','9999','3','Semanal','151','4' UNION ALL

SELECT '125','619618','Almacenes Exito S.A.','155','95','6','Exito Bucaramanga # 149','Giron (Santander)','4022.980','1','3','Semanal','152','5' UNION ALL

SELECT '126','621903','Almacenes Exito S.A.','155','95','2','Ley Tunja # 580','Tunja (Boyaca)','1122.740','2','3','Semanal','94','48' UNION ALL

SELECT '127','647571','Almacenes Exito S.A.','155','95','2','Ley Duitama # 574','Duitama (Boyaca)','779.680','2','3','Semanal','95','49' UNION ALL

SELECT '128','651463','Almacenes Exito S.A.','155','95','6','Exito Bucaramanga Centro # 322','Bucaramanga (Santander)','1616.740','1','3','Semanal','153','6' UNION ALL

SELECT '129','651586','Almacenes Exito S.A.','155','95','2','Ley Somagoso # 578','Sogamoso (Boyaca)','649.730','1','3','Semanal','96','50' UNION ALL

SELECT '130','653450','Grandes Superficies de Colombi','1652','95','6','Carrefour Bucaramanga','Bucaramanga (Santander)','1347.290','9999','3','Semanal','154','7' UNION ALL

SELECT '131','659706','Grandes Superficies De Colombi','1652','95','6','Carrefour Ventura','Cucuta (Norte De Santander)','1940.090','9999','3','Semanal','155','8' UNION ALL

SELECT '132','696707','Almacenes Exito S.A.','155','95','6','Exito Oriental # 352','Floridablanca (Santander)','1940.090','1','3','Semanal','156','9' UNION ALL

SELECT '133','733144','Makro Supermayorista S. A.','720','94','4','Makro Supermayorista S. A.','Medellin (Antioquia)','4827.580','9999','2','Quincenal','147','13' UNION ALL

SELECT '134','818992','Makro Supermayorista S. A.','720','94','3','Makro Villa Santos','Barranquilla (Atlantico)','3352.490','9999','1','Quincenal','127','28' UNION ALL

SELECT '135','828574','Makro Supermayorista S. A.','720','94','2','Makro Cumara 2','Bogota D.C.','3352.490','9999','4','Quincenal','97','51' UNION ALL

SELECT '136','837419','Olimpica S.A.','135','95','3','Olimpica Monteria # 302','Monteria (Cordoba)','3352.490','1','1','Semanal','128','29' UNION ALL

SELECT '137','841680','Olimpica S.A.','135','95','3','Olimpica # 251','Valledupar (Cesar)','2793.740','2','1','Semanal','129','30' UNION ALL

SELECT '138','883200','Makro Supermayorista S. A.','720','94','1','Makro Supermayorista S. A.','Cali (Valle)','3352.490','9999','6','Quincenal','35','35' UNION ALL

SELECT '139','894159','Olimpica S.A.','135','95','2','Supertienda Olimpica Cll 100','Bogota D.C.','2328.110','2','4','Semanal','98','52' UNION ALL

SELECT '140','894531','Olimpica S.A.','135','95','2','Sao Plaza De Las Americas #405','Bogota D.C.','5793.100','1','4','Semanal','99','53' UNION ALL

SELECT '141','899573','Olimpica S.A.','135','95','3','Sao # 93','Barranquilla (Atlantico)','3352.490','1','1','Semanal','130','31' UNION ALL

SELECT '142','899887','Olimpica S.A.','135','95','3','Sao # 53','Barranquilla (Atlantico)','3352.490','1','1','Semanal','131','32' UNION ALL

SELECT '143','903573','Olimpica S.A.','135','95','3','Olimpica La Pajuela # 321','Sincelejo (Sucre)','1616.740','1','1','Semanal','132','33' UNION ALL

SELECT '144','903696','Olimpica S.A.','135','95','3','Olimpica # 201','Santa Marta (Magdalena)','1122.740','2','1','Semanal','133','34' UNION ALL

SELECT '145','903719','Olimpica S.A.','135','95','3','Olimpica Bocagrande # 103','Cartagena (Bolivar)','1616.740','2','1','Semanal','134','35' UNION ALL

SELECT '146','945887','Almacenes Exito S.A.','155','95','1','Exito Popayan','Popayan (Cauca)','3352.490','1','6','Semanal','36','36' UNION ALL

SELECT '147','974353','Surtifamiliar S.A.','101','94','1','Surtifamiliar S.a. Tulua','Tulua (Valle)','2793.740','9999','6','Quincenal','37','37' UNION ALL

SELECT '148','975004','Olimpica S.A.','135','95','1','Olimpica Pereira # 351','Pereira (Risaralda)','1122.740','2','5','Semanal','38','38' UNION ALL

SELECT '149','975011','Olimpica S.A.','135','95','1','Olimpica Manizales # 361','Manizales (Caldas)','451.200','2','5','Semanal','39','39' UNION ALL

SELECT '150','975134','Olimpica S.A.','135','95','1','Olimpica Tequendama # 550','Cali (Valle)','779.680','2','6','Semanal','40','40' UNION ALL

SELECT '151','975141','Olimpica S.A.','135','95','1','Olimpica Buga # 553','Buga (Valle)','1347.290','2','6','Semanal','41','41' UNION ALL

SELECT '152','975172','Olimpica S.A.','135','95','1','Olimpica Tulua # 554','Tulua (Valle)','779.680','2','6','Semanal','42','42' UNION ALL

SELECT '153','975189','Olimpica S.A.','135','95','1','Olimpica Buenaventura # 557','Buenaventura (Valle)','1122.740','2','6','Semanal','43','43' UNION ALL

SELECT '154','975202','Olimpica S.A.','135','95','1','Sao 358 Portal Del Quindio','Armenia (Quindio)','1616.740','1','5','Semanal','44','44' UNION ALL

SELECT '155','975264','Olimpica S.A.','135','95','1','Olimpica Barranquilla # 570','Cali (Valle)','649.730','2','6','Semanal','45','45' UNION ALL

SELECT '156','975561','Surtifamiliar S.A.','101','94','1','Surtifamiliar Buga S. A.','Buga (Valle)','2328.110','9999','6','Quincenal','46','46' UNION ALL

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

Hi Jorge, can you post the CREATE TABLE statement for dbo.sipDimensionPos please?

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

datelligence Say Hey Kid Points: 672 More actions · Answer 8

HI Chris,

--===== Create table

CREATE TABLE #sipDimensionPos(

[id_pos] [bigint] NULL,

[id_cliente] [bigint] NOT NULL,

[razonSocial_cliente] [varchar](100) NULL,

[id_CliPadre] [int] NULL,

[nombre_CliPadre] [varchar](100) NULL,

[id_canal] [smallint] NULL,

[id_sucursal] [smallint] NULL,

[establecimiento] [varchar](100) NULL,

[nombre_Localidad] [varchar](100) NULL,

[ponderado_gondola] [numeric](10, 3) NULL,

[id_formato] [int] NULL,

[id_sucNielsen] [int] NULL,

[Periodicidad] [varchar](15) NULL,

[ttal] [numeric](15, 0) NULL,

[numero_Registro] [numeric](10, 0) NULL,

CONSTRAINT [PK_sipDimensionPos] PRIMARY KEY CLUSTERED

(

[id_cliente] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

-- @PivotCliPadres populated

-- with distinct values, and in order

DECLARE @PivotCliPadres VARCHAR(1000)

SELECT @PivotCliPadres = COALESCE(

@PivotCliPadres + ',[' + cast(id_CliPadre as varchar) + ']',

'[' + cast(id_CliPadre as varchar)+ ']')

FROM (

SELECT id_CliPadre

FROM #sipDimensionPos

GROUP BY id_CliPadre

) d

ORDER BY d.id_CliPadre

-- check

SELECT @PivotCliPadres

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

-- Sample data setup for #sipDimensionPos

--===== Create table

DROP TABLE #sipDimensionPos

CREATE TABLE #sipDimensionPos(

[id_pos] [bigint] NULL,

[id_cliente] [bigint] NOT NULL,

[razonSocial_cliente] [varchar](100) NULL,

[id_CliPadre] [int] NULL,

[nombre_CliPadre] [varchar](100) NULL,

[id_canal] [smallint] NULL,

[id_sucursal] [smallint] NULL,

[establecimiento] [varchar](100) NULL,

[nombre_Localidad] [varchar](100) NULL,

[ponderado_gondola] [numeric](10, 3) NULL,

[id_formato] [int] NULL,

[id_sucNielsen] [int] NULL,

[Periodicidad] [varchar](15) NULL,

[ttal] [numeric](15, 0) NULL,

[numero_Registro] [numeric](10, 0) NULL,

CONSTRAINT [PK_sipDimensionPos] PRIMARY KEY CLUSTERED

(

[id_cliente] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

--===== Insert the test data into the test table

INSERT INTO #sipDimensionPos (

[id_pos],

[id_cliente],

[razonSocial_cliente],

[id_CliPadre],

[nombre_CliPadre],

--[id_canal],

[id_sucursal],

[establecimiento],

[nombre_Localidad],

[ponderado_gondola],

[id_formato],

[id_sucNielsen],

[Periodicidad],

[ttal],

[numero_Registro])

SELECT '1','21004','Almacenes Exito S.A.','155','95','1','Exito San Fernando # 051','Cali (Valle)','5793.100','1','6','Semanal','1','1' UNION ALL

SELECT '2','92011','Almacenes La 14 S.A.','110','95','1','La 14 Av Sexta # 5','Cali (Valle)','10010.480','9999','6','Semanal','2','2' UNION ALL

SELECT '3','92097','Almacenes La 14 S.A.','110','95','1','La 14 Buenaventura # 15','Buenaventura (Valle)','8342.060','9999','6','Semanal','3','3' UNION ALL

SELECT '4','92103','Almacenes La 14 S.A.','110','95','1','La 14 Pereira # 23','Pereira (Risaralda)','4022.980','9999','5','Semanal','4','4' UNION ALL

SELECT '5','92875','Almacenes La 14 S.A.','110','95','1','La 14 Calima # 8','Cali (Valle)','12012.570','9999','6','Semanal','5','5' UNION ALL

SELECT '6','100399','Almacenes Exito S.A.','155','95','1','Exito Unicentro Cali','Cali (Valle)','4022.980','1','6','Semanal','6','6' UNION ALL

SELECT '7','100879','Almacenes Exito S.A.','155','95','1','Almacenes Exito Buenaventura','Buenaventura (Valle)','261.000','1','6','Semanal','7','7' UNION ALL

SELECT '8','128199','Almacenes Exito S.A.','155','95','1','Exito Pasto','Pasto (Nariño)','2793.740','1','6','Semanal','8','8' UNION ALL

SELECT '9','131256','Almacenes La 14 S.A.','110','95','1','La 14 Tulua # 27','Tulua (Valle)','2328.110','9999','6','Semanal','9','9' UNION ALL

SELECT '10','135275','Caja Comp. Fliar. Comfandi','115','95','1','Comfandi Decepaz','Cali (Valle)','1616.000','9999','6','Semanal','10','10' UNION ALL

SELECT '11','138265','Alvarado Parra Carlos Jose','624','94','1','Supermercado Mercacentro # 4','Ibague (Tolima)','4827.580','9999','5','Quincenal','11','11' UNION ALL

SELECT '12','141535','Grandes Superf. Decolombia','1652','95','1','Carrefour Pereira','Pereira (Risaralda)','5793.100','9999','5','Semanal','12','12' UNION ALL

SELECT '13','147573','Olimpica Sao 380 Neiva','135','95','1','Olimpica Neiva # 380','Neiva (Huila)','1347.290','1','5','Semanal','13','13' UNION ALL

SELECT '14','148109','Almacenes La 14 S.A.','110','95','1','La 14 Pasoancho # 33','Cali (Valle)','10010.480','9999','6','Semanal','14','14' UNION ALL

SELECT '15','155687','Grandes Superficies De Colombi','1652','95','1','Carrefour Buga','Buga (Valle)','1616.740','9999','6','Semanal','15','15' UNION ALL

SELECT '16','156071','Caja Comp. Fliar. Comfandi','115','95','1','Comfandi Morichal','Cali (Valle)','1347.290','9999','6','Semanal','16','16' UNION ALL

SELECT '17','158851','Almacenes Exito S.A.','155','95','2','Exito Calle 80','Bogota D.C.','8342.060','1','4','Semanal','47','1' UNION ALL

SELECT '18','158868','Almacenes Exito S.A.','155','95','2','Exito Norte # 092','Bogota D.C.','6951.720','1','4','Semanal','48','2' UNION ALL

SELECT '19','158875','Almacenes Exito S.A.','155','95','2','Exito Americas # 084','Bogota D.C.','8342.060','1','4','Semanal','49','3' UNION ALL

SELECT '20','158907','Almacenes Exito S.A.','155','95','1','Exito Neiva # 157','Neiva (Huila)','4827.580','1','5','Semanal','17','17' UNION ALL

SELECT '21','161356','Almacenes Yep S.A.','130','94','2','Almacen Yep Villavicencio','Villavicencio (Meta)','541.440','9999','3','Semanal','50','4' UNION ALL

SELECT '22','161509','Almacenes Yep S.A.','130','94','1','Almacenes Yep Neiva','Neiva (Huila)','1940.090','9999','5','Quincenal','18','18' UNION ALL

SELECT '23','164246','Grandes Superficies De Colombi','1652','95','1','Carrefour Chipichape','Cali (Valle)','3352.490','9999','6','Semanal','19','19' UNION ALL

SELECT '24','171391','Grandes Superfices De Colombia','1652','95','1','Carrefour Ibague','Ibague (Tolima)','4022.980','9999','5','Semanal','20','20' UNION ALL

SELECT '25','171823','Grandes Superficies De Colombi','1652','95','1','Carrefour Valle Del Lili','Cali (Valle)','4827.580','9999','6','Semanal','21','21' UNION ALL

SELECT '26','173675','Olimpica S.a.','135','95','1','Supermercado Olimpica Sto 359','Armenia (Quindio)','451.200','2','5','Semanal','22','22' UNION ALL

SELECT '27','174519','Almacenes Exito S.A.','155','95','1','Ley San Cancio Manizales # 420','Manizales (Caldas)','779.680','2','5','Semanal','23','23' UNION ALL

SELECT '28','175045','Grandes Superficies De Colombi','1652','95','1','Carrefour Neiva','Neiva (Huila)','4022.980','9999','5','Semanal','24','24' UNION ALL

SELECT '29','176973','Almacenes Exito S.A.','155','95','1','Ley Pereira Centro # 424','Pereira (Risaralda)','935.610','2','5','Semanal','25','25' UNION ALL

SELECT '30','177700','Almacenes Exito S.A.','155','95','1','Exito La Flora # 054','Cali (Valle)','4022.980','1','6','Semanal','26','26' UNION ALL

SELECT '31','189690','Almacenes Exito S.A.','155','95','1','Exito Ibague # 156','Ibague (Tolima)','4022.980','1','5','Semanal','27','27' UNION ALL

SELECT '32','192090','Almacenes Exito S.A.','155','95','1','Exito Tulua','Tulua (Valle)','1122.740','9999','6','Semanal','28','28' UNION ALL

SELECT '33','192579','Almacenes Exito S.A.','155','95','1','Ley Armenia # 628','Armenia (Quindio)','649.730','2','5','Semanal','29','29' UNION ALL

SELECT '34','193358','Comerc.Giraldo Y Gomez Cia S A','1676','94','1','Superinter Autoservicio Siloe','Cali (Valle)','2328.110','9999','6','Quincenal','30','30' UNION ALL

SELECT '35','198022','Colsubsidio','233','95','2','Supermercado Calle 26','Bogota D.C.','5793.100','9999','4','Semanal','51','5' UNION ALL

SELECT '36','198046','Colsubsidio','233','95','2','Supermercado Calle 63','Bogota D.C.','1940.090','9999','4','Semanal','52','6' UNION ALL

SELECT '37','198350','Caja Comp. Fliar. Cafam','120','95','2','Supermercado Nuevo Kennedy','Bogota D.C.','3352.490','9999','4','Semanal','53','7' UNION ALL

SELECT '38','198351','Almacenes Exito S.A.','155','95','1','Exito Pereira #063','Pereira (Risaralda)','4028.980','1','5','Semanal','31','31' UNION ALL

SELECT '39','198367','Caja Comp. Fliar. Cafam','120','95','2','Supermercado Floresta','Bogota D.C.','8342.060','9999','4','Semanal','54','8' UNION ALL

SELECT '40','198451','Grandes Superficies De Colombi','1652','95','1','Carrefour Popayan','Popayan (Cauca)','1122.740','9999','6','Semanal','32','32' UNION ALL

SELECT '41','198664','Caja Comp. Fliar. Cafam','120','95','2','Supermecado 20 De Julio','Bogota D.C.','1940.090','9999','4','Semanal','55','9' UNION ALL

SELECT '42','202020','Almacenes Exito S.A.','155','95','4','Exito Colombia # 031','Medellin (Antioquia)','10010.480','1','2','Semanal','135','1' UNION ALL

SELECT '43','202037','Almacenes Exito S.A.','155','95','4','Exito Envigado # 035','Envigado (Antioquia)','10010.480','1','2','Semanal','136','2' UNION ALL

SELECT '44','202051','Almacenes Exito S.A.','155','95','4','Exito Poblado # 033','Medellin (Antioquia)','12012.570','1','2','Semanal','137','3' UNION ALL

SELECT '45','202068','Almacenes Exito S.A.','155','95','4','Exito San Antonio # 039','Medellin (Antioquia)','10010.480','1','2','Semanal','138','4' UNION ALL

SELECT '46','205617','Grandes Superficies De Colombi','1652','95','2','Carrefour Carrera 30','Bogota D.C.','10010.480','9999','4','Semanal','56','10' UNION ALL

SELECT '47','206161','Almacenes Exito S.A.','155','95','2','Carulla Castilla','Bogota D.C.','1940.090','8','4','Semanal','57','11' UNION ALL

SELECT '48','206246','Almacenes Exito S.A.','155','95','2','Carulla Country','Bogota D.C.','5793.100','8','4','Semanal','58','12' UNION ALL

SELECT '49','206284','Almacenes Exito S.A.','155','95','2','Carulla Galerias','Bogota D.C.','4022.980','8','4','Semanal','59','13' UNION ALL

SELECT '50','206420','Almacenes Exito S.A.','155','95','2','Carulla Pablo VI','Bogota D.C.','2793.740','8','4','Semanal','60','14' UNION ALL

SELECT '51','209520','Colsubsidio','233','95','2','Superm. Unicentro Occidente','Bogota D.C.','4022.980','9999','4','Semanal','61','15' UNION ALL

SELECT '52','211743','Colsubsidio','233','95','2','COLSUBSIDIO Nueva Zelandia','Bogota D.C.','1616.740','9999','4','Semanal','62','16' UNION ALL

SELECT '53','214143','Caja Comp. Fliar. Cafam','120','95','2','Minimercado Express Cll 85 Del','Bogota D.C.','1347.000','9999','4','Semanal','63','17' UNION ALL

SELECT '54','214436','Almacenes Exito S.A.','155','95','2','Exito Gran Estacion','Bogota D.C.','4022.980','1','4','Semanal','64','18' UNION ALL

SELECT '55','220149','Almacenes Exito S.A.','155','95','2','Exito Colina # 088','Bogota D.C.','8342.060','1','4','Semanal','65','19' UNION ALL

SELECT '56','225428','Almacenes Exito S.A.','155','95','2','Exito Chapinero','Bogota D.C.','8342.060','1','4','Semanal','66','20' UNION ALL

SELECT '57','226990','Olimpica S.A.','135','95','2','Supertienda Olimpica Unisur','Soacha (Cundinamarca)','1347.290','2','4','Semanal','67','21' UNION ALL

SELECT '58','228578','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Prado Veranieg','Bogota D.C.','1940.090','7','4','Semanal','68','22' UNION ALL

SELECT '59','229666','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Avenida Rojas','Bogota D.C.','935.610','7','4','Semanal','69','23' UNION ALL

SELECT '60','230680','Olimpica S.A.','135','95','2','Sao Portal De La 80 #432','Bogota D.C.','3352.490','1','4','Semanal','70','24' UNION ALL

SELECT '61','234548','Grandes Superficies De Colombi','1652','95','2','Carrefour Villavicencio','Villavicencio (Meta)','3352.490','9999','3','Semanal','71','25' UNION ALL

SELECT '62','234664','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Primavera','Bogota D.C.','1616.740','7','4','Semanal','72','26' UNION ALL

SELECT '63','238031','Caja Comp. Fliar. Del Valle D','115','95','1','Comfandi Guadalupe','Cali (Valle)','4022.980','9999','6','Semanal','33','33' UNION ALL

SELECT '64','238176','El Arrozal Y Cia S. En C.','1152','94','2','El Arrozal Estrada','Bogota D.C.','1616.740','9999','4','Quincenal','73','27' UNION ALL

SELECT '65','239391','Almacenes Exito S.A.','155','95','2','Exito Country # 081','Bogota D.C.','12012.570','1','4','Semanal','74','28' UNION ALL

SELECT '66','243103','Almacenes Exito S.A.','155','95','2','Exito Unicentro Bogota # 303','Bogota D.C.','8342.060','1','4','Semanal','75','29' UNION ALL

SELECT '67','249308','Almacenes Exito S.A.','155','95','2','Exito Villa Mayor # 083','Bogota D.C.','6951.720','1','4','Semanal','76','30' UNION ALL

SELECT '68','250011','Grandes Superficies De Colombi','1652','95','2','Carrefour Autopista Sur','Bogota D.C.','5793.100','9999','4','Semanal','77','31' UNION ALL

SELECT '69','251789','Almacenes Exito S.A.','155','95','2','Bodega Surtimax Calle 80','Bogota D.C.','1347.290','7','4','Semanal','78','32' UNION ALL

SELECT '70','252807','Olimpica S.A.','135','95','2','Supertiendaolimpicacolinaca418','Bogota D.C.','3352.490','2','4','Semanal','79','33' UNION ALL

SELECT '71','255057','Grandes Superficies De Colombi','1652','95','2','Carrefour Santa Ana','Bogota D.C.','10010.480','9999','4','Semanal','80','34' UNION ALL

SELECT '72','257475','Almacenes Exito S.A.','155','95','2','Carulla Pepe Sierra','Bogota D.C.','8342.060','8','4','Semanal','81','35' UNION ALL

SELECT '73','259295','Colombiana De Comercio S.A.','489','94','2','Alkosto Villavicencio','Villavicencio (Meta)','3352.490','9999','3','Quincenal','82','36' UNION ALL

SELECT '74','259707','Colombiana De Comercio S.A.','489','94','2','Alkosto Avenida 68','Bogota D.C.','6951.720','9999','4','Quincenal','83','37' UNION ALL

SELECT '75','260060','Colombiana De Comercio S.A.','489','94','2','Alkosto Carrera 30','Bogota D.C.','3352.490','9999','4','Quincenal','84','38' UNION ALL

SELECT '76','261536','Almacenes Exito S.A.','155','95','2','Carulla Calle 140','Bogota D.C.','6951.720','1','4','Semanal','85','39' UNION ALL

SELECT '77','264054','Grandes Superficies De Colombi','1652','95','2','Carrefour 170','Bogota D.C.','10010.480','9999','4','Semanal','86','40' UNION ALL

SELECT '78','277062','El Arrozal y Cia S. En C.','1152','94','2','Lider San Pablo','Bogota D.C.','2328.110','9999','4','Quincenal','87','41' UNION ALL

SELECT '79','277555','Grandes Superficies De Colombi','1652','95','2','Carrefour Suba','Bogota D.C.','5793.100','9999','4','Semanal','88','42' UNION ALL

SELECT '80','278840','Grandes Superficies De Colombi','1652','95','2','Carrefour Calle 80','Bogota D.C.','12012.570','9999','4','Semanal','89','43' UNION ALL

SELECT '81','284858','Almacenes Exito S.A.','155','95','3','Exito 366 Murillo','Barranquilla (Atlantico)','1940.090','1','1','Semanal','100','1' UNION ALL

SELECT '82','293046','Coop. De Consumo De Antioquia','240','94','4','Consumo America','Medellin (Antioquia)','60.720','9999','2','Quincenal','139','5' UNION ALL

SELECT '83','295000','Almacenes Exito S.A.','155','95','2','Bodega Surtimax San Blas','Bogota D.C.','1616.740','7','4','Semanal','90','44' UNION ALL

SELECT '84','295352','Almacenes Exito S.A.','155','95','2','Exito Suba','Bogota D.C.','4022.980','1','4','Semanal','91','45' UNION ALL

SELECT '85','296206','Grandes Superficies De Colombi','1652','95','2','Carrefour Hayuelos','Bogota D.C.','10010.480','9999','4','Semanal','92','46' UNION ALL

SELECT '86','301790','Almacenes Exito S.A.','155','95','3','Exito 369 Panorama','Barranquilla (Atlantico)','1940.090','1','1','Semanal','101','2' UNION ALL

SELECT '87','312570','Grandes Superficies de Colombi','1652','95','3','Carrefour C/gena. Caribe Plaza','Cartagena (Bolivar)','4022.980','9999','1','Semanal','102','3' UNION ALL

SELECT '88','315059','Almacenes Exito S.A.','155','95','3','Exito 370 Castellana Cartagena','Cartagena (Bolivar)','3352.490','1','1','Semanal','103','4' UNION ALL

SELECT '89','317058','Almacenes Exito S.A.','155','95','3','Exito 367 San Diego C/gena','Cartagena (Bolivar)','1616.740','1','1','Semanal','104','5' UNION ALL

SELECT '90','318915','Almacenes Exito S.A.','155','95','3','Exito 368 San Francisco','Barranquilla (Atlantico)','2328.110','1','1','Semanal','105','6' UNION ALL

SELECT '91','334300','Grandes Superficies de Colombi','1652','95','3','Carrefour Americano','Barranquilla (Atlantico)','779.680','9999','1','Semanal','106','7' UNION ALL

SELECT '92','336244','Grandes Superficies de Colombi','1652','95','3','Carrefour Guatapuri','Valledupar (Cesar)','4022.000','9999','1','Semanal','107','8' UNION ALL

SELECT '93','346594','Almacenes Exito S.A.','155','95','3','Exito San Blas','Barranquilla (Atlantico)','935.610','1','1','Semanal','108','9' UNION ALL

SELECT '94','347500','Olimpica S.A.','135','95','1','Olimpica Central','Popayan (Cauca)','779.680','1','6','Semanal','34','34' UNION ALL

SELECT '95','353699','Almacenes Exito S.A.','155','95','3','Exito CentroSanta Marta','Santa Marta (Magdalena)','1616.740','1','1','Semanal','109','10' UNION ALL

SELECT '96','357495','Almacenes Exito S.A.','155','95','3','Exito Barranquilla','Barranquilla (Atlantico)','4827.580','1','1','Semanal','110','11' UNION ALL

SELECT '97','366084','Almacenes Exito S.A.','155','95','3','Ley Valledupar # 266','Valledupar (Cesar)','1940.090','1','1','Semanal','111','12' UNION ALL

SELECT '98','372158','Almacenes Exito S.A.','155','95','3','Exito Sincelejo','Sincelejo (Sucre)','2328.110','1','1','Semanal','112','13' UNION ALL

SELECT '99','375264','Grandes Superficies de Colombi','1652','95','3','Carrefour Monteria','Monteria (Cordoba)','1940.090','9999','1','Semanal','113','14' UNION ALL

SELECT '100','379085','Almacenes Exito S.A.','155','95','3','Exito 363 Buenavista St. Marta','Santa Marta (Magdalena)','1616.740','1','1','Semanal','114','15' UNION ALL

SELECT '101','380523','Almacenes Exito S.A.','155','95','3','Exito Barranquilla Sur','Barranquilla (Atlantico)','1940.090','1','1','Semanal','115','16' UNION ALL

SELECT '102','387238','Almacenes Exito S.A.','155','95','3','Exito Alamedas del Sinu 357','Monteria (Cordoba)','2793.740','1','1','Semanal','116','17' UNION ALL

SELECT '103','389907','Almacenes Exito S.A.','155','95','3','Exito Ejecutivos # 257','Cartagena (Bolivar)','1347.290','1','1','Semanal','117','18' UNION ALL

SELECT '104','391635','Olimpica S.A.','135','95','3','Sao # 031 Hipodromo','Soledad (Atlantico)','2793.740','1','1','Semanal','118','19' UNION ALL

SELECT '105','391680','Olimpica S.A.','135','95','3','Olimpica Rodadero # 202','Santa Marta (Magdalena)','1347.290','2','1','Semanal','119','20' UNION ALL

SELECT '106','391727','Olimpica S.A.','135','95','3','Sao Santa Marta # 203','Santa Marta (Magdalena)','2328.110','1','1','Semanal','120','21' UNION ALL

SELECT '107','391925','Olimpica S.A.','135','95','3','Olimpica # 514','Barranquilla (Atlantico)','2328.110','3','1','Semanal','121','22' UNION ALL

SELECT '108','391956','Olimpica S.A.','135','95','3','Olimpica Pie De Popa # 107','Cartagena (Bolivar)','1616.740','2','1','Semanal','122','23' UNION ALL

SELECT '109','392096','Olimpica S.A.','135','95','3','Sao Plazuela # 105','Cartagena (Bolivar)','3352.490','1','1','Semanal','123','24' UNION ALL

SELECT '110','392327','Grandes Superficies De Colombi','1652','95','3','Carrefour Barranquilla','Barranquilla (Atlantico)','4827.580','9999','1','Semanal','124','25' UNION ALL

SELECT '111','392340','Grandes Superficiesde Colombia','1652','95','3','Carrefour Santa Marta','Santa Marta (Magdalena)','4022.980','9999','1','Semanal','125','26' UNION ALL

SELECT '112','393751','Nuevo Mercadefam S.A.','487','95','6','Mercadefam Cabecera','Bucaramanga (Santander)','10010.480','9999','3','Semanal','148','1' UNION ALL

SELECT '113','396707','Almacenes Exito S.A.','155','95','3','Exito 354 las Flores V/dupar','Valledupar (Cesar)','2328.110','1','1','Semanal','126','27' UNION ALL

SELECT '114','400177','Almacenes Exito S.A.','155','95','4','Exito Bello # 030','Bello (Antioquia)','5793.100','1','2','Semanal','140','6' UNION ALL

SELECT '115','432420','Grandes Superficies De Colombi','1652','95','4','Carrefour Cra 65','Medellin (Antioquia)','8342.060','9999','2','Semanal','141','7' UNION ALL

SELECT '116','451334','Almacenes Exito S.A.','155','95','4','Carulla Belen','Medellin (Antioquia)','1347.290','8','2','Semanal','142','8' UNION ALL

SELECT '117','452088','Almacenes Exito S.A.','155','95','4','Carulla America # 11','Medellin (Antioquia)','1940.090','8','2','Semanal','143','9' UNION ALL

SELECT '118','453910','Coop. De Consumo De Antioquia','240','94','4','Consumo Envigado','Envigado (Antioquia)','104.930','9999','2','Quincenal','144','10' UNION ALL

SELECT '119','463157','Almacenes Exito S.A.','155','95','4','Carulla San Ignacio','Medellin (Antioquia)','1940.090','8','2','Semanal','145','11' UNION ALL

SELECT '120','488584','Almacenes Exito S.A.','155','95','4','Exito Laureles # 037','Medellin (Antioquia)','10010.480','1','2','Semanal','146','12' UNION ALL

SELECT '121','604172','Almacenes Exito S.A.','155','95','6','Exito San Mateo','Cucuta (Norte De Santander)','1940.090','1','3','Semanal','149','2' UNION ALL

SELECT '122','604481','Grandes Superficies de Colombi','1652','95','6','Carefour Floridablanca','Floridablanca (Santander)','1347.000','9999','3','Semanal','150','3' UNION ALL

SELECT '123','610259','Grandes Superficies De Colombi','1652','95','2','Carrefour Tunja','Tunja (Boyaca)','1940.090','9999','3','Semanal','93','47' UNION ALL

SELECT '124','617172','Grandes Superficies de Colombi','1652','95','6','Carrefour Giron','Giron (Santander)','1177.000','9999','3','Semanal','151','4' UNION ALL

SELECT '125','619618','Almacenes Exito S.A.','155','95','6','Exito Bucaramanga # 149','Giron (Santander)','4022.980','1','3','Semanal','152','5' UNION ALL

SELECT '126','621903','Almacenes Exito S.A.','155','95','2','Ley Tunja # 580','Tunja (Boyaca)','1122.740','2','3','Semanal','94','48' UNION ALL

SELECT '127','647571','Almacenes Exito S.A.','155','95','2','Ley Duitama # 574','Duitama (Boyaca)','779.680','2','3','Semanal','95','49' UNION ALL

SELECT '128','651463','Almacenes Exito S.A.','155','95','6','Exito Bucaramanga Centro # 322','Bucaramanga (Santander)','1616.740','1','3','Semanal','153','6' UNION ALL

SELECT '129','651586','Almacenes Exito S.A.','155','95','2','Ley Somagoso # 578','Sogamoso (Boyaca)','649.730','1','3','Semanal','96','50' UNION ALL

SELECT '130','653450','Grandes Superficies de Colombi','1652','95','6','Carrefour Bucaramanga','Bucaramanga (Santander)','1347.290','9999','3','Semanal','154','7' UNION ALL

SELECT '131','659706','Grandes Superficies De Colombi','1652','95','6','Carrefour Ventura','Cucuta (Norte De Santander)','1940.090','9999','3','Semanal','155','8' UNION ALL

SELECT '132','696707','Almacenes Exito S.A.','155','95','6','Exito Oriental # 352','Floridablanca (Santander)','1940.090','1','3','Semanal','156','9' UNION ALL

SELECT '133','733144','Makro Supermayorista S. A.','720','94','4','Makro Supermayorista S. A.','Medellin (Antioquia)','4827.580','9999','2','Quincenal','147','13' UNION ALL

SELECT '134','818992','Makro Supermayorista S. A.','720','94','3','Makro Villa Santos','Barranquilla (Atlantico)','3352.490','9999','1','Quincenal','127','28' UNION ALL

SELECT '135','828574','Makro Supermayorista S. A.','720','94','2','Makro Cumara 2','Bogota D.C.','3352.490','9999','4','Quincenal','97','51' UNION ALL

SELECT '136','837419','Olimpica S.A.','135','95','3','Olimpica Monteria # 302','Monteria (Cordoba)','3352.490','1','1','Semanal','128','29' UNION ALL

SELECT '137','841680','Olimpica S.A.','135','95','3','Olimpica # 251','Valledupar (Cesar)','2793.740','2','1','Semanal','129','30' UNION ALL

SELECT '138','883200','Makro Supermayorista S. A.','720','94','1','Makro Supermayorista S. A.','Cali (Valle)','3352.490','9999','6','Quincenal','35','35' UNION ALL

SELECT '139','894159','Olimpica S.A.','135','95','2','Supertienda Olimpica Cll 100','Bogota D.C.','2328.110','2','4','Semanal','98','52' UNION ALL

SELECT '140','894531','Olimpica S.A.','135','95','2','Sao Plaza De Las Americas #405','Bogota D.C.','5793.100','1','4','Semanal','99','53' UNION ALL

SELECT '141','899573','Olimpica S.A.','135','95','3','Sao # 93','Barranquilla (Atlantico)','3352.490','1','1','Semanal','130','31' UNION ALL

SELECT '142','899887','Olimpica S.A.','135','95','3','Sao # 53','Barranquilla (Atlantico)','3352.490','1','1','Semanal','131','32' UNION ALL

SELECT '143','903573','Olimpica S.A.','135','95','3','Olimpica La Pajuela # 321','Sincelejo (Sucre)','1616.740','1','1','Semanal','132','33' UNION ALL

SELECT '144','903696','Olimpica S.A.','135','95','3','Olimpica # 201','Santa Marta (Magdalena)','1122.740','2','1','Semanal','133','34' UNION ALL

SELECT '145','903719','Olimpica S.A.','135','95','3','Olimpica Bocagrande # 103','Cartagena (Bolivar)','1616.740','2','1','Semanal','134','35' UNION ALL

SELECT '146','945887','Almacenes Exito S.A.','155','95','1','Exito Popayan','Popayan (Cauca)','3352.490','1','6','Semanal','36','36' UNION ALL

SELECT '147','974353','Surtifamiliar S.A.','101','94','1','Surtifamiliar S.a. Tulua','Tulua (Valle)','2793.740','9999','6','Quincenal','37','37' UNION ALL

SELECT '148','975004','Olimpica S.A.','135','95','1','Olimpica Pereira # 351','Pereira (Risaralda)','1122.740','2','5','Semanal','38','38' UNION ALL

SELECT '149','975011','Olimpica S.A.','135','95','1','Olimpica Manizales # 361','Manizales (Caldas)','451.200','2','5','Semanal','39','39' UNION ALL

SELECT '150','975134','Olimpica S.A.','135','95','1','Olimpica Tequendama # 550','Cali (Valle)','779.680','2','6','Semanal','40','40' UNION ALL

SELECT '151','975141','Olimpica S.A.','135','95','1','Olimpica Buga # 553','Buga (Valle)','1347.290','2','6','Semanal','41','41' UNION ALL

SELECT '152','975172','Olimpica S.A.','135','95','1','Olimpica Tulua # 554','Tulua (Valle)','779.680','2','6','Semanal','42','42' UNION ALL

SELECT '153','975189','Olimpica S.A.','135','95','1','Olimpica Buenaventura # 557','Buenaventura (Valle)','1122.740','2','6','Semanal','43','43' UNION ALL

SELECT '154','975202','Olimpica S.A.','135','95','1','Sao 358 Portal Del Quindio','Armenia (Quindio)','1616.740','1','5','Semanal','44','44' UNION ALL

SELECT '155','975264','Olimpica S.A.','135','95','1','Olimpica Barranquilla # 570','Cali (Valle)','649.730','2','6','Semanal','45','45' UNION ALL

SELECT '156','975561','Surtifamiliar S.A.','101','94','1','Surtifamiliar Buga S. A.','Buga (Valle)','2328.110','9999','6','Quincenal','46','46'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

datelligence Say Hey Kid Points: 672 More actions · Answer 11

Ok Chris, the result is this:

[101],[110],[115],[120],[130],[135],[155],[233],[240],[487],[489],[624],[720],[1152],[1652],[1676]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Cool - now plug this new component into your query set in place of the original one which was unordered and giving you dupes.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

datelligence Say Hey Kid Points: 672 More actions · Answer 13

Hi Chris, so the whole query looks like this, even do how do I query the pivoted data?

--===== Create table

DROP TABLE #sipDimensionPos

CREATE TABLE #sipDimensionPos(

[id_pos] [bigint] NULL,

[id_cliente] [bigint] NOT NULL,

[razonSocial_cliente] [varchar](100) NULL,

[id_CliPadre] [int] NULL,

[nombre_CliPadre] [varchar](100) NULL,

[id_canal] [smallint] NULL,

[id_sucursal] [smallint] NULL,

[establecimiento] [varchar](100) NULL,

[nombre_Localidad] [varchar](100) NULL,

[ponderado_gondola] [numeric](10, 3) NULL,

[id_formato] [int] NULL,

[id_sucNielsen] [int] NULL,

[Periodicidad] [varchar](15) NULL,

[ttal] [numeric](15, 0) NULL,

[numero_Registro] [numeric](10, 0) NULL,

CONSTRAINT [PK_sipDimensionPos] PRIMARY KEY CLUSTERED

(

[id_cliente] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

--===== Insert the test data into the test table

INSERT INTO #sipDimensionPos (

[id_pos],

[id_cliente],

[razonSocial_cliente],

[id_CliPadre],

[nombre_CliPadre],

--[id_canal],

[id_sucursal],

[establecimiento],

[nombre_Localidad],

[ponderado_gondola],

[id_formato],

[id_sucNielsen],

[Periodicidad],

[ttal],

[numero_Registro])