It Must Be Possible?!

• Jonathan Conway

  SSC Rookie

  Points: 30

  More actions

  August 28, 2006 at 1:49 am

  #68433

  Hey, Folks

  I'm a bit green when it comes to SQL Server.

  I have a View that I use to power a Crystal Report (newbie at Crystal too).

  I want to have one field in the report that will display a list of values, seperated by comma - ',' all in one field. The values come from another table.

  I'm a bit lost -- where to I do this? From the SQL end or the Reports end? Whats the syntax to concatenate all the values from a table into one string?

  Cheers!

  - Jonathan

• Deon Crause

  SSC Rookie

  Points: 29

  More actions

  August 28, 2006 at 2:31 am

  #657026

  Hi Johathan,

  I think it is called pivoting a table. e.g.

  DELCARE @test-2 VARCHAR(256)

  SET @test-2 = ''

  SELECT  @test-2=@Test+[ColumnName]+','

  FROM [Table]

  PRINT @test-2

  Maybe try and place the code in UDF and call it from the view......

   

  Regards

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  August 28, 2006 at 4:05 pm

  #657213

  Explain the problem clearly.. I dont think its pivoting a table.. if that is the case I saw so many solutions in this forum only

• Jonathan Conway

  SSC Rookie

  Points: 30

  More actions

  August 28, 2006 at 5:36 pm

  #657219

  Hey,

  Thanks for the input, I found a solution similar to what Deon said:

  ///

  DECLARE

  @data varchar(100)

  SELECT

  @data = COALESCE(@data + ', ', '') +

  CAST(Name AS varchar(100))

  FROM

  tblPartnerInstitute

  SELECT

  @data

  \\\

  OT: If anyone here has any Crystal Reports experience -- is there any way to change the data source of the report without having all the fields deleted (it took me 30 min to create them)? If not, there should be!....

• Bill Scott

  SSCrazy

  Points: 2338

  More actions

  August 29, 2006 at 12:07 pm

  #657329

  OT: If anyone here has any Crystal Reports experience -- is there any way to change the data source of the report without having all the fields deleted (it took me 30 min to create them)? If not, there should be!....

   

  There is, in your database menu choose Set DataSource Location and navigate to your new SP/Table/View in the Replace With box, choose the item you are replacing in the Current Data Source box and then click the Update button.   If the column names have changed, it will throw up a dialog to allow you to map the old names to the new names.  My old version 9 doesn't always update the new stored procedure name or the column names, but it does fetch the updated data.