Post reply

It Must Be Possible?!

  • 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

  • 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

  • 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

  • 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!....

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply