displaying multiple fields together as one

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 2005 Forums

Transact-SQL (2005)

displaying multiple fields together as one

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-08-03 : 15:18:44

Hi:

I need to have a column on a report display 6 fields in one column. The data displayed would look like this:

51,16,140,0,0,0
53,15,145,0,0,0
49,17,130,0,0,0
etc....

The formats are: float (Product_Details.Prddtl_Frame_Eye), smallint (Product_Details.Prddtl_Frame_Dbl), nvarchar (Product_Details.Prddtl_Frame_Temple), float (Product_Details.Prddtl_Frame_A) , float (Product_Details.Prddtl_Frame_B) and float (Product_Details.Prddtl_Frame_ED) . The errors I get refer to converting these to something other than what they are.

I've tried a number of approaches - the latest of which was substring - all of which failed miserably. I know there has to be a very simple way of doing this so I am hoping someone here can help me. Here is my latest code with the substrings.

 select 
   Vendor.Vendor_Name1	                'Vendor',
   Product.Prd_Desc		        'Product',
   Product.Prd_Style_Name	        'Product Code',
   Product_Details.Prddtl_UPC_Code	'UPC',
   substring(Product_Details.Prddtl_Frame_Eye,1,2) + ',' +
    substring(Product_Details.Prddtl_Frame_Dbl,1,2) + ',' +
    substring(Product_Details.Prddtl_Frame_Temple,1,3) + ',' +
    substring(Product_Details.Prddtl_Frame_A,1,1) + ',' +
    substring(Product_Details.Prddtl_Frame_B,1,1) + ',' +
    substring(Product_Details.Prddtl_Frame_ED,1,1) + ','  'Size',
   Product_Details.Prddtl_Color_Desc	'Color',
   Product_Details.Prddtl_Qty_On_Hand	'On Hand',
   Product_Details.Prddtl_Qty_On_Order	'On Order',
   Product.Prd_Frames_Cost		'Cost',
	Product.Prd_Cost			'Value'

from Product
join Product_Details
   on Product_Details.Prd_No = Product.Prd_No
join Vendor
   on Vendor.Vendor_No = Product.Vendor_no

order by 
  Vendor.Vendor_Name1,
  Product.Prd_Desc

Any help you can provide is greatly appreciated.

Thanks!
Sherri

SLReid
Forum Newbie
Renton, WA USA

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-03 : 15:33:52

Try to CAST each of your values as CHAR or VARCHAR and then do a substring...somthing like below..

SELECT   vendor.vendor_name1                                                                                                                                                                                                                                                                                        'Vendor', 
         product.prd_desc                                                                                                                                                                                                                                                                                           'Product', 
         product.prd_style_name                                                                                                                                                                                                                                                                                     'Product Code', 
         product_details.prddtl_upc_code                                                                                                                                                                                                                                                                            'UPC', 
         Substring(Cast(product_details.prddtl_frame_eye AS CHAR(10)), 
                   1,2) + ',' + Substring(product_details.prddtl_frame_dbl,1,2) + ',' + Substring(product_details.prddtl_frame_temple,1,3) + ',' + Substring(product_details.prddtl_frame_a,1,1) + ',' + Substring(product_details.prddtl_frame_b,1,1) + ',' + Substring(product_details.prddtl_frame_ed,1,1) + ',' 'Size', 
         product_details.prddtl_color_desc                                                                                                                                                                                                                                                                          'Color', 
         product_details.prddtl_qty_on_hand                                                                                                                                                                                                                                                                         'On Hand', 
         product_details.prddtl_qty_on_order                                                                                                                                                                                                                                                                        'On Order', 
         product.prd_frames_cost                                                                                                                                                                                                                                                                                    'Cost', 
         product.prd_cost                                                                                                                                                                                                                                                                                           'Value' 
FROM     product 
         JOIN product_details 
           ON product_details.prd_no = product.prd_no 
         JOIN vendor 
           ON vendor.vendor_no = product.vendor_no 
ORDER BY vendor.vendor_name1, 
         product.prd_desc