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 2012 Forums
 Transact-SQL (2012)
 Query Problem

Author  Topic 

BitsAndBytes
Starting Member

10 Posts

Posted - 2015-01-28 : 07:16:39
Good morning everyone.

I have a problem that I hope you can help me with. I have a column called "DistributionMethod" in a table that can have any of the three following values, CROSS-DOCK, DISTRIBUION, DROPSHIPMENT. In the row that contains the column DistributionMethod there is also a column called "VendorNo". A column called DirectCost which is currency is also in the row. The problem I face is that depending on what value is in VendorNo I am to display the currency value in DirectCost in a column with the header of either PALLET, EACH, HALF-TRUCK, FULL-TRUCK or DROPSHIPMENT which we thought were going to be in the "DistributionMethod" field(they're not in there). And to make it worse we need these values in tabular form.

So for example if in a row if vendorNo is '123' then put the value of Diretcost $55.25 for example under the column header TRUCK. And the header TRUCK does not exist.

If vendorNo is '537' then put the value of DirectCost $2.12 for example under the column header EACH. And the header Each does not exist. And so on and so on. I have around one hundred of these VendorNo to code this way by the way.

Is it possible to write this in SQL? If it is possible would someone please give me a hand with this. I'd really appreciate it.


gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 09:30:53
it's possible. you do something like this:


select case when vendorNo = '123' then DirectCost end as TRUCK,
case when vendorNo = '537' then DirectCost end as EACH,
... etc. ...


Are you saying that your final output will have 100 columns? I sure wouldn't want to be the person reading it!
Go to Top of Page

BitsAndBytes
Starting Member

10 Posts

Posted - 2015-01-28 : 20:09:11
No not 100 columns. 100 test conditions. Thanks for the help :)
Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-29 : 09:17:46
if it is 100 test conditions then you can maintain those conditions in a table and join that table in this query to get the required output.

Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -