Query to return membership from a table

• Cory Slaney

  SSCrazy

  Points: 2459

  More actions

  September 22, 2005 at 9:17 am

  #91320

  I have a table with the following structure:

  CREATE TABLE [dbo].[profiles] (

   [profile_id] [numeric](8, 0) IDENTITY (1, 1) NOT NULL ,

   [username] [varchar] (15) NOT NULL ,

   [password] [varchar] (8) NOT NULL ,

   [name] [varchar] (30) NOT NULL ,

   [company_id] [numeric](3, 0) NOT NULL ,

   [address1] [varchar] (25) NULL ,

   [address2] [varchar] (25) NULL ,

   [city] [varchar] (15) NULL ,

   [prov] [varchar] (2) NULL ,

   [postal_code] [varchar] (7) NULL ,

   [phone] [varchar] (10) NULL ,

   [fax] [varchar] (10) NULL ,

    [varchar] (50) NULL ,

   [status] [varchar] (1) NOT NULL ,

   [pymt] [varchar] (1) NOT NULL ,

   [access_method] [varchar] (15) NULL ,

   [billing_id] [numeric](2, 0) NOT NULL ,

   [bkn] [numeric](1, 0) NOT NULL ,

   [bks] [numeric](1, 0) NOT NULL ,

   [bkg] [numeric](1, 0) NOT NULL ,

   [bkb] [numeric](1, 0) NOT NULL ,

   [aes] [numeric](1, 0) NOT NULL ,

   [sar] [numeric](1, 0) NOT NULL ,

   [ccg] [numeric](1, 0) NOT NULL ,

   [pci] [numeric](1, 0) NOT NULL ,

   [otr] [numeric](1, 0) NOT NULL ,

   [bkq] [numeric](1, 0) NOT NULL ,

   [dnd] [numeric](1, 0) NOT NULL ,

   [iac] [numeric](1, 0) NOT NULL ,

   [amo] [numeric](1, 0) NOT NULL ,

   [hmd] [numeric](1, 0) NOT NULL ,

   [usc] [numeric](1, 0) NOT NULL ,

   [pat] [numeric](1, 0) NOT NULL ,

   [schedule] [numeric](1, 0) NOT NULL ,

   [general] [numeric](1, 0) NOT NULL ,

   [mission] [numeric](1, 0) NOT NULL ,

   [news] [numeric](1, 0) NOT NULL ,

   [photo] [numeric](1, 0) NOT NULL ,

   [quality] [numeric](1, 0) NOT NULL ,

   [quick] [numeric](1, 0) NOT NULL ,

   [real] [numeric](1, 0) NOT NULL ,

    [numeric](1, 0) NOT NULL ,

   [fullres] [numeric](1, 0) NOT NULL ,

   [quickview] [numeric](1, 0) NOT NULL ,

   [wrs] [numeric](1, 0) NOT NULL ,

   [logstat] [numeric](1, 0) NOT NULL ,

   [incident] [numeric](1, 0) NOT NULL ,

   [page4] [numeric](1, 0) NOT NULL ,

   [page5] [numeric](1, 0) NOT NULL ,

   [sess_id] [int] NULL ,

   127.0.0.1 [varchar] (15) NULL ,

   [esd] [numeric](1, 0) NULL ,

   [cab] [numeric](1, 0) NULL ,

   [dre] [numeric](1, 0) NULL ,

   [viewnews] [numeric](1, 0) NULL ,

   [a_fo] [bit] NULL ,

   [a_a] [bit] NULL ,

   [a_bkn] [bit] NULL ,

   [a_bks] [bit] NULL ,

   [a_bkg] [bit] NULL ,

   [a_bkb] [bit] NULL ,

   [a_bkq] [bit] NULL ,

   [a_rep] [bit] NULL ,

   [ais] [bit] NULL ,

   [tca] [bit] NULL

  ) ON [PRIMARY]

  GO

  There is a record for each user in the system. I would like to return all the relevant personal information and their memberhip in the bolded columns above (represented by values of 1 or 0) as one column with a comma-delimited list of values (1,0,1,1,1,0,0,0,0,0,0,1,1,0). Any help would be appreciated?

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 22, 2005 at 9:53 am

  #592307

  Select CAST(bkn as CHAR(1)) + ',' + CAST(bks AS CHAR(1))...

  Why would you want to do that exactly?