Is it possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is it possible?

Author

Topic

waldis
Starting Member

21 Posts

Posted - 2010-12-09 : 06:54:44

Let's say I have these fields (I broke them into levels for easier view, but all of them are one rwo in the table):

id,
ProductA,ProductB,ProductC,ProductD,ProductE,
PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg,
cn100,cn95,cn50,cn10,
c_white,c_black,c_red,c_green

By using query I need to create multiple rows for each existing row in the table.

The resulting output table should look like this (before each field I'm adding a new field name with value derived from the next field name, e.g. 'A' AS Product; the next field contains a value from actual field, so F1v will contain values from f1a,f1b,f1c,f1d,f1e, etc.) Each level must loop through all fields below and create one row each:


id,Product,ProductValue,ProdSize,ProdSizeValue,CN,CNvalue,Color,ColorValue (Headers)

10,A,ProductA,Small,PA_sm,100,cn100,White,c_white
10,A,ProductA,Small,PA_sm,100,cn100,Black,c_black
10,A,ProductA,Small,PA_sm,100,cn100,Red,c_red
10,A,ProductA,Small,PA_sm,100,cn100,Green,c_green
10,A,ProductA,Small,PA_sm,95,cn95,White,c_white
10,A,ProductA,Small,PA_sm,95,cn95,Black,c_black
10,A,ProductA,Small,PA_sm,95,cn95,Red,c_red
10,A,ProductA,Small,PA_sm,95,cn95,Green,c_green
10,A,ProductA,Small,PA_sm,50,cn50,White,c_white
10,A,ProductA,Small,PA_sm,50,cn50,Black,c_black
10,A,ProductA,Small,PA_sm,50,cn50,Red,c_red
10,A,ProductA,Small,PA_sm,50,cn50,Green,c_green
10,A,ProductA,Small,PA_sm,10,cn10,White,c_white
10,A,ProductA,Small,PA_sm,10,cn10,Black,c_black
10,A,ProductA,Small,PA_sm,10,cn10,Red,c_red
10,A,ProductA,Small,PA_sm,10,cn10,Green,c_green
10,.............................................
10,B,ProductB,Small,PA_sm,100,cn100,White,c_white
10,B,ProductB,Small,PA_sm,100,cn100,Black,c_black
10,B,ProductB,Small,PA_sm,100,cn100,Red,c_red
10,B,ProductB,Small,PA_sm,100,cn100,Green,c_green
10,.............................................
10,E,ProductE,XLarge,PA_xlg,10,cn10,White,c_white
10,E,ProductE,XLarge,PA_xlg,10,cn10,Black,c_black
10,E,ProductE,XLarge,PA_xlg,10,cn10,Red,c_red
10,E,ProductE,XLarge,PA_xlg,10,cn10,Green,c_green

Plus on top of that it has to exclude any rows where there is value of 0 in all fields for the current row in the new output.

Any clues on how could I accomplish this?

Thanks,

Waldis

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 07:59:12

Not sure what you want but it's almost certainly possible.
Can you give an example with a couple of products and attributes - you can extrapolate from there.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

waldis
Starting Member

21 Posts

Posted - 2010-12-09 : 08:43:49

I'll plug some numbers in, and I replaced one column's values to be preceded with 'CN-' to avoid confusion.

This is the comma delimited values from the table:

id,ProductA,ProductB,ProductC,ProductD,ProductE,PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,PD_sm,PD_med,PD_lg,PD_xlg,
PE_sm,PE_med,PE_lg,PE_xlg,cn100,cn95,cn50,cn10,c_white,c_black,c_red,c_green (Headers)
10,95,5,0,0,0,100,0,0,0,0,100,0,0,0,0,100,0,0,0,0,100,0,0,100,0,90,10,0,0,90,5,5,0

This would be how the new values would be written out from the single row above:

id,Product,ProductValue,ProdSize,ProdSizeValue,CN,CNvalue,Color,ColorValue (Headers)
10,A,95,Small,100,CN-100,90,White,90
10,A,95,Small,100,CN-100,90,Black,5
10,A,95,Small,100,CN-100,90,Red,5
10,A,95,Small,100,CN-100,90,Green,0
10,A,95,Small,95,CN-95,10,White,90
10,A,95,Small,95,CN-95,10,Black,5
10,A,95,Small,95,CN-95,10,Red,5
10,A,95,Small,95,CN-95,10,Green,0

...and so on.

I envision something like this, maybe?

for each [id] loop
  for each [ProductA,ProductB,ProductC,ProductD,ProductE] loop
    for each [PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg] loop
      for each [cn100,cn95,cn50,cn10] loop
        for each [c_white,c_black,c_red,c_green] loop
          if (ProductValue * ProdSizeValue * CNvalue * ColorValue) is not 0 then
            write out the row
        end loop
      end loop
    end loop
  end loop
end loop

But I do not know how to script it in SQL.

I hope I'm explaining it clear enough.

Thanks,

Waldis

Edit: I had one column in the wrong place, corrected.

waldis
Starting Member

21 Posts

Posted - 2010-12-09 : 10:54:48

I suppose I could do it this way, but that means I have to write one select statement for each row, and there's going to be alot of them:

(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-100' as CN, 
     cn100 as CNvalue, 
     'White' as Color, 
     c_white as ColorValue, 
 FROM dbo.Products) 
 UNION 
(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-100' as CN, 
     cn100 as CNvalue, 
     'Black' as Color, 
     c_black as ColorValue, 
 FROM dbo.Products) 
 UNION 
(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-100' as CN, 
     cn100 as CNvalue, 
     'Red' as Color, 
     c_red as ColorValue, 
 FROM dbo.Products) 
 UNION 
(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-100' as CN, 
     cn100 as CNvalue, 
     'Green' as Color, 
     c_green as ColorValue, 
 FROM dbo.Products) 
 UNION 
(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-95' as CN, 
     cn95 as CNvalue, 
     'White' as Color, 
     c_white as ColorValue, 
 FROM dbo.Products) 
 UNION 
(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-95' as CN, 
     cn95 as CNvalue, 
     'Black' as Color, 
     c_black as ColorValue, 
 FROM dbo.Products) 
 UNION 
(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-95' as CN, 
     cn95 as CNvalue, 
     'Red' as Color, 
     c_red as ColorValue, 
 FROM dbo.Products) 
 UNION 
(SELECT  
     Id, 
     'A' as Product, 
     ProductA as ProductValue, 
     'Small' as ProdSize, 
     PA_sm as ProdSizeValue, 
     'CN-95' as CN, 
     cn95 as CNvalue, 
     'Green' as Color, 
     c_green as ColorValue, 
 FROM dbo.Products) 
 UNION 
etc., etc., etc. ...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-09 : 14:13:33

Here's a proof of concept that's a little shorter than the huge union:

declare @products table(id int,ProductA int,ProductB int,ProductC int,ProductD int,ProductE int,PA_sm int,PA_med int,PA_lg int,PA_xlg int,
PB_sm int,PB_med int,PB_lg int,PB_xlg int,PC_sm int,PC_med int,PC_lg int, PC_xlg int,PD_sm int,PD_med int,PD_lg int,PD_xlg int,
PE_sm int,PE_med int,PE_lg int,PE_xlg int,cn100 int,cn95 int,cn50 int,cn10 int,c_white int,c_black int,c_red int,c_green int)

insert @products select 10,95,5,0,0,0,100,0,0,0,0,100,0,0,0,0,100,0,0,0,0,100,0,0,100,0,90,10,0,0,90,5,5,0;

with -- start here, replace "@products" below with your table name
	prods(id,Product,ProductValue) as 
		(select id,replace(Product,'Product',''),Value from 
			(select id,ProductA,ProductB,ProductC,ProductD,ProductE from @products) a 
				unpivot(Value FOR Product IN(ProductA,ProductB,ProductC,ProductD,ProductE)) b)
	,colors(id,Color,ColorValue) as 
	(select id,replace(Color,'c_',''),Value from 
		(select id,c_white,c_black,c_red,c_green from @products) a 
			unpivot(Value FOR Color IN(c_white,c_black,c_red,c_green)) b)
	,CN(id,CN,CNValue) as 
			(select id,replace(CN,'cn','CN-'),Value from 
				(select id,cn100,cn95,cn50,cn10 from @products) a 
					unpivot(Value FOR CN IN(cn100,cn95,cn50,cn10)) b)
	,Sizes(id,Product,ProdSize,ProdSizeValue) as 
			(select id,Substring(Size,2,1) Product, 
				replace(replace(replace(replace(Stuff(Size,1,3,''),'sm','Small'),
					'xlg','XtraLarge'),'med','Medium'),'lg','Large'), Value from 
					(select id,PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
						PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg from @products) a 
							unpivot(Value FOR Size IN(PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
								PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg)) b)
select p.ID, p.Product, p.ProductValue, s.ProdSize, s.ProdSizeValue, CN.CN, CN.CNValue, C.Color, C.ColorValue from prods p
inner join colors c on p.id=c.id
inner join CN cn on p.id=cn.id
inner join sizes s on p.id=s.id and p.product=s.product
order by p.id, p.product, cn.CN, c.color desc

Not sure if the data is correct, you'll have to validate that. (do that before asking me how it works)

waldis
Starting Member

21 Posts

Posted - 2010-12-11 : 09:51:13

Thanks, Robvolk! It'll take me some time to wrap my brain around what you did...

By my calculations the total row output should be 1,600; for each id (1) there is (*5) Product (*20) ProdSize (*4) CN (*4) Color = 1,600 rows, am I miscalculating something? It gets to 1 * 5 * 20 = 80 and then CN and Color has the same 80 values (there seems to be missing rows - for each ProdSize *4 CN, and going deeper, for each CN *4 Color). Please correct me if I'm wrong.

Thanks very much,

Val

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-11 : 11:55:34

quote:
for each id (1) there is (*5) Product (*20) ProdSize (*4) CN (*4) Color = 1,600 rows, am I miscalculating something?

I understood the PA/PB/PC etc. prefixes to mean they only applied to specific products, PA = Product A only, and so on. So there are only 4 sizes per product (see 2 column join on the Sizes portion of the CTE). If that's not the case you can just join on ID and it should generate the "missing" rows.

The general idea behind UNPIVOT is a shorthand syntax of the large UNION you proposed. Books Online has an example that's a bit easier to digest. I'm still not sure this is 100% right, I'd recommend looking at the output of each CTE section individually and validate it's correct.

waldis
Starting Member

21 Posts

Posted - 2010-12-12 : 09:57:43

They all are sub-entries of previous entries, thus possibly generating up to 1,600 rows.

By Books Online are you referring to this: http://onlinebooks.library.upenn.edu/lists.html or this: http://www.booksonline.com/ or any other website? I guess I'm not sure what are you referring to.

I have to look at UNPIVOT.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-12 : 12:27:55

Books Online is installed with SQL Server, in the Documentation section of its program group. There also an internet link, here's the entry for UNPIVOT:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

To get the 1600 row option, change the join on Sizes to: inner join sizes s on p.id=s.id

waldis
Starting Member

21 Posts

Posted - 2010-12-12 : 15:42:55

Well, you were right, I was wrong! When I started to digging it deeper I realized that it can't be 1*5*20*4*4, that will add sizes for all product groups to every product group (A will have all sizes for A, B, C, D, and E); it has to be 1*5*4*4*4, that's why you added "p.product=s.product" to inner join, which insures that each product gets the correct (matching) product's sizes (A gets all sizes for A, B for B, etc.)

I guess I haven't installed Online Books with SQL Server 2008, at least I cannot find it anywhere - but I can use the MSDN Library link that you gave.

Learn something new every day...

In the end, the code you wrote worked like a charm and all data validates so far.

Where do I send beer?

Thanks,

Waldis

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-12 : 19:22:27

Beer can be left on the forums like so:

waldis
Starting Member

21 Posts

Posted - 2010-12-15 : 10:44:57

Now I'm facing a next challenge. I want to add a column that multiplies values from a field in the row (the original row) with the values from the generated rows and creating a new field at the end.

I have added an additional field Volume right after id field. If I just multiply the values in select statement it throws an error:
--------------------------
(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type int.
--------------------------

How can I declare calcVolume to be bigint? I do not want to change any data type in the source table assignments.

declare @products table(id int,Volume int,ProductA int,ProductB int,ProductC int,ProductD int,ProductE int,PA_sm int,PA_med int,PA_lg int,PA_xlg int,
PB_sm int,PB_med int,PB_lg int,PB_xlg int,PC_sm int,PC_med int,PC_lg int, PC_xlg int,PD_sm int,PD_med int,PD_lg int,PD_xlg int,
PE_sm int,PE_med int,PE_lg int,PE_xlg int,cn100 int,cn95 int,cn50 int,cn10 int,c_white int,c_black int,c_red int,c_green int)

insert @products select 1,750000,11,12,13,14,15,21,22,23,24,31,32,33,34,41,42,43,44,51,52,53,54,61,62,63,64,101,102,103,104,201,202,203,204;

with -- start here, replace "@products" below with your table name
	prods(id,Volume,Product,ProductValue) as 
		(select id,Volume,replace(Product,'Product',''),Value from 
			(select id,Volume,ProductA,ProductB,ProductC,ProductD,ProductE from @products) a 
				unpivot(Value FOR Product IN(ProductA,ProductB,ProductC,ProductD,ProductE)) b)
	,colors(id,Color,ColorValue) as 
	(select id,replace(Color,'c_',''),Value from 
		(select id,c_white,c_black,c_red,c_green from @products) a 
			unpivot(Value FOR Color IN(c_white,c_black,c_red,c_green)) b)
	,CN(id,CN,CNValue) as 
			(select id,replace(CN,'cn','CN-'),Value from 
				(select id,cn100,cn95,cn50,cn10 from @products) a 
					unpivot(Value FOR CN IN(cn100,cn95,cn50,cn10)) b)
	,Sizes(id,Product,ProdSize,ProdSizeValue) as 
			(select id,Substring(Size,2,1) Product, 
				replace(replace(replace(replace(Stuff(Size,1,3,''),'sm','Small'),
					'xlg','XtraLarge'),'med','Medium'),'lg','Large'), Value from 
					(select id,PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
						PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg from @products) a 
							unpivot(Value FOR Size IN(PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
								PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg)) b)
select p.ID, p.Product, p.ProductValue, s.ProdSize, s.ProdSizeValue, CN.CN, CN.CNValue, C.Color, C.ColorValue
	,p.Volume*p.ProductValue*s.ProdSizeValue*CN.CNValue*c.ColorValue as CalcVolume from prods p
inner join colors c on p.id=c.id
inner join CN cn on p.id=cn.id
inner join sizes s on p.id=s.id and p.product=s.product
order by p.id, p.product, cn.CN, c.color desc

But that's not all. If I want to introduce brand and brandValue to the source table, like so:

b_1,b_2,b_3,b_4,b_5,bAllOther_6,v_1Val,b_2Val,b_3Val,b_4Val,b_5Val,bAllOther_6Val

How would I show both values alongside (no field names needed in rows)? For simplicity, let's say I need only Product, ProductValue, ProdSize, ProdSizeValue, Brand (values from b_1,...,b_AllOther_6), and BrandValue (values from b_1Val,...,b_AllOther_6Val).

I never got outside just create and select statements, so this is a whole new ball game to me. I really appreciate your help.

Why is the SQL logic so different from other languages, at times it just doesn't make sense... (e.g. you assign variable somewhere way down in the function, but you start using it in the beginning, etc.)

Thanks,

Waldis

waldis
Starting Member

21 Posts

Posted - 2010-12-15 : 11:54:57

Ok, I might be unto something here. Please tell me if I did it corectly. If that is a correct way of doing it (it looks like the data aligns), the only thing to figure out is how do I add p.Volume into calculation for CalcVolume column.

declare @products table(id int,Volume int,ProductA int,ProductB int,ProductC int,ProductD int,ProductE int,PA_sm int,PA_med int,PA_lg int,PA_xlg int,
PB_sm int,PB_med int,PB_lg int,PB_xlg int,PC_sm int,PC_med int,PC_lg int, PC_xlg int,PD_sm int,PD_med int,PD_lg int,PD_xlg int,
PE_sm int,PE_med int,PE_lg int,PE_xlg int,cn100 int,cn95 int,cn50 int,cn10 int,c_white int,c_black int,c_red int,c_green int,
b_1 varchar(50),b_2 varchar(50),b_3 varchar(50),b_4 varchar(50),b_5 varchar(50),bAllOther_6 varchar(50),
b_1Val int,b_2Val int,b_3Val int,b_4Val int,b_5Val int,bAllOther_6Val int)

insert @products select 1,750000,11,12,13,14,15,21,22,23,24,31,32,33,34,41,42,43,44,51,52,53,54,61,62,63,64,101,102,103,104,201,202,203,204
	,'Brand_1','Brand_2','Brand_3','Brand_4','Brand_5','Brand_6',5,6,7,8,9,65;

with -- start here, replace "@products" below with your table name
	prods(id,Volume,Product,ProductValue) as 
		(select id,Volume,replace(Product,'Product',''),Value from 
			(select id,Volume,ProductA,ProductB,ProductC,ProductD,ProductE from @products) a 
				unpivot(Value FOR Product IN(ProductA,ProductB,ProductC,ProductD,ProductE)) b)
	,Sizes(id,Product,ProdSize,ProdSizeValue) as 
			(select id,Substring(Size,2,1) Product, 
				replace(replace(replace(replace(Stuff(Size,1,3,''),'sm','Small'),
					'xlg','XtraLarge'),'med','Medium'),'lg','Large'), Value from 
					(select id,PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
						PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg from @products) a 
							unpivot(Value FOR Size IN(PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
								PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg)) b)
	,Brand(id,Customer,CustomerValue) as 
			(select id,replace(Cust,'Val','')
				,Value from (select id,b_1Val,b_2Val,b_3Val,b_4Val,b_5Val,bAllOther_6Val from @products) a
					unpivot(Value FOR Cust IN(b_1Val,b_2Val,b_3Val,b_4Val,b_5Val,bAllOther_6Val)) b)

	,Brand2(id,Customer,CustomerName) as 
			(select id,CustN
				,Value from (select id,b_1,b_2,b_3,b_4,b_5,bAllOther_6 from @products) a
					unpivot(Value FOR CustN IN(b_1,b_2,b_3,b_4,b_5,bAllOther_6)) b)

select p.ID, p.Product, p.ProductValue, s.ProdSize, s.ProdSizeValue,b2.CustomerName, b.CustomerValue
	,(p.ProductValue*s.ProdSizeValue*b.CustomerValue) as CalcVolume from prods p
		--Need to add * p.Volume to (p.ProductValue*s.ProdSizeValue*b.CustomerValue) as CalcVolume
inner join Brand b on p.id=b.id
inner join Brand2 b2 on b.id=b2.id and b.Customer=b2.Customer
inner join sizes s on p.id=s.id and p.product=s.product
order by p.id, p.product, b2.CustomerName asc

waldis
Starting Member

21 Posts

Posted - 2010-12-15 : 15:19:11

I created function and hoped it'll help:

CREATE FUNCTION fx_CalcVolume
( @volume int, @prodVal int, @sizeVal int, @custValue int )
RETURNS bigint
AS
BEGIN
	RETURN (@volume * @prodVal * @sizeVal * @custValue)
END
GO

for some reason it keeps being underlined in Management Studio when I add it to the final select statement as follows:

... dbo.fx_CalcVolume(p.Volume, b.CustomerValue, s.ProdSizeValue, p.ProductValue) ...

I do add 'USE myDbName' at the top so it would knoww where to look for the function though.

Regardless of underlining it parses, and when I Execute - it gives me the same dreaded error message:

(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 13
Arithmetic overflow error converting expression to data type int.

Does it completely ignore the bigint that I show as a return data type?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-15 : 15:23:17

This is a placeholder for me, I can't look at this right now but I'll try to get back to you later today.

waldis
Starting Member

21 Posts

Posted - 2010-12-16 : 11:24:55

Casting the values this way seems to work. The only question is why do I have to restate calculation in where clause - it's not letting me to use CalcVolume <> 0 instead, why is it saying it's invalid column name, isn't it declared in the same select statement?

declare @products table(id int,Volume int,ProductA int,ProductB int,ProductC int,ProductD int,ProductE int,PA_sm int,PA_med int,PA_lg int,PA_xlg int,
PB_sm int,PB_med int,PB_lg int,PB_xlg int,PC_sm int,PC_med int,PC_lg int, PC_xlg int,PD_sm int,PD_med int,PD_lg int,PD_xlg int,
PE_sm int,PE_med int,PE_lg int,PE_xlg int,cn100 int,cn95 int,cn50 int,cn10 int,c_white int,c_black int,c_red int,c_green int,
b_1 varchar(50),b_2 varchar(50),b_3 varchar(50),b_4 varchar(50),b_5 varchar(50),bAllOther_6 varchar(50),
b_1Val int,b_2Val int,b_3Val int,b_4Val int,b_5Val int,bAllOther_6Val int)

insert @products select 1,750000,11,12,13,14,15,21,22,23,24,31,32,33,34,41,42,43,44,51,52,53,54,61,62,63,64,101,102,103,104,201,202,203,204
	,'Brand_1','Brand_2','Brand_3','Brand_4','Brand_5','Brand_6',5,6,7,8,9,65;

with -- start here, replace "@products" below with your table name
	prods(id,Volume,Product,ProductValue) as 
		(select id,Volume,replace(Product,'Product',''),Value from 
			(select id,Volume,ProductA,ProductB,ProductC,ProductD,ProductE from @products) a 
				unpivot(Value FOR Product IN(ProductA,ProductB,ProductC,ProductD,ProductE)) b)
	,Sizes(id,Product,ProdSize,ProdSizeValue) as 
			(select id,Substring(Size,2,1) Product, 
				replace(replace(replace(replace(Stuff(Size,1,3,''),'sm','Small'),
					'xlg','XtraLarge'),'med','Medium'),'lg','Large'), Value from 
					(select id,PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
						PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg from @products) a 
							unpivot(Value FOR Size IN(PA_sm,PA_med,PA_lg,PA_xlg,PB_sm,PB_med,PB_lg,PB_xlg,PC_sm,PC_med,PC_lg,PC_xlg,
								PD_sm,PD_med,PD_lg,PD_xlg,PE_sm,PE_med,PE_lg,PE_xlg)) b)
	,Brand(id,Customer,CustomerValue) as 
			(select id,replace(Cust,'Val','')
				,Value from (select id,b_1Val,b_2Val,b_3Val,b_4Val,b_5Val,bAllOther_6Val from @products) a
					unpivot(Value FOR Cust IN(b_1Val,b_2Val,b_3Val,b_4Val,b_5Val,bAllOther_6Val)) b)

	,Brand2(id,Customer,CustomerName) as 
			(select id,CustN
				,Value from (select id,b_1,b_2,b_3,b_4,b_5,bAllOther_6 from @products) a
					unpivot(Value FOR CustN IN(b_1,b_2,b_3,b_4,b_5,bAllOther_6)) b)

select p.ID, p.Product, p.ProductValue, s.ProdSize, s.ProdSizeValue,b2.CustomerName, b.CustomerValue
	,(cast(p.Volume as bigint)*cast(p.ProductValue as bigint)*cast(s.ProdSizeValue as bigint)*cast(b.CustomerValue as bigint))
		as CalcVolume from prods p
inner join Brand b on p.id=b.id
inner join Brand2 b2 on b.id=b2.id and b.Customer=b2.Customer
inner join sizes s on p.id=s.id and p.product=s.product
where (cast(p.Volume as bigint)*cast(p.ProductValue as bigint)*cast(s.ProdSizeValue as bigint)*cast(b.CustomerValue as bigint)) <> 0
order by p.id, p.product, b2.CustomerName asc

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-16 : 16:17:10

You cannot reference a column alias in the WHERE clause. However, you can define the last query as another CTE with CalcVolume as a defined column, then add select * from NewCTE WHERE CalcVolume<>0.

waldis
Starting Member

21 Posts

Posted - 2010-12-16 : 17:52:58

Yep, that did it!

Thanks, Rob!

waldis
Starting Member

21 Posts

Posted - 2010-12-17 : 21:53:10

I'm trying to output the data by using bcp. I have created stored procedures that I need and they output the data.
When I try to output it with bcp, it gives me an error that the object, which is my stored procedure, is invalid.
It works fine if I use table in place of stored procedure. Am I doing it all wrong?

-- Export data from My_Stored_Procedure
DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp [my-db-01].[dbo].[My_Stored_Procedure] out e:\CSV_Exports\sProcOut.csv -c -t"|" -T'
EXEC "my-db-01"..xp_cmdshell @sql
GO


Output:

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'my-db-01.dbo.My_Stored_Procedure'.
NULL


==============================
==============================
==============================


-- Export data from MyTable
DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp [my-db-01].[dbo].[MyTable] out e:\CSV_Exports\TableOut.csv -c -t"|" -T'
EXEC "my-db-01"..xp_cmdshell @sql
GO


Output:

NULL
Starting copy...
NULL
14 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 16     Average : (875.00 rows per sec.)
NULL