Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: selecting Dynamic Columns

    I have the following Tables:

    Table1: Row1
    ----------------------------------
    Name Gary
    Garbage1 A
    Garbage2 B

    Table2: Row1 Row2 Row3
    ------------------------------------------------------------------
    Name Gary Gary Gary
    Value 1 2 3
    Desc Day Afternoon Night

    Table1 has a 1 to Many relationship with Table2

    Id Like to have the select statement Return the data as follows:

    Gary,A,B,Day,1,Afternoon,2,Night,3

    not like this:
    Gary A,B,Day,1
    Gary A,B,Afternoon,2
    Gary A,B,Night,3

    Any Ideas?

  2. #2
    Join Date
    Jul 2004
    Location
    Irvine, CA
    Posts
    7
    You want all of the rows to come back as one row in a set?
    A) Uh, why?
    B) I am hoping these tables don't have very many rows
    c) Assuming that you REALLY want to do that anyways, the only way I could really come up with would be a temp table and a cursor (ugly). Here it is:

    CREATE PROCEDURE resultsNeeded
    AS
    CREATE TABLE #working (
    Name varchar(50),
    Garbage1 varchar(50),
    Garbage2 varchar(50),
    [Value1] varchar(50),
    [Desc1] varchar(50)
    )

    DECLARE @maxresults int,
    @foundresults int,
    @Name varchar(50),
    @Value varchar(50),
    @Desc varchar(50),
    @LastName varchar(50),
    @ColumnName varchar(50),
    @SQL varchar(8000)

    SET @foundresults = 0
    SET @maxresults = 1
    SET @LastName="Something that will not be in there?"

    INSERT INTO #working (
    Name,
    Garbage1,
    Garbage2)
    SELECT
    Name,
    Garbage1,
    Garbage2
    FROM Table1

    DECLARE tabletwo_cursor CURSOR FOR
    SELECT [Name], [Value], [Desc] FROM Table2
    ORDER BY Name

    OPEN tabletwo_cursor

    FETCH NEXT FROM tabletwo_cursor INTO
    @Name,
    @Value,
    @Desc

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @LastName <> @Name
    SET @foundresults = 1
    ELSE
    SET @foundresults = @foundresults + 1

    SET @ColumnName = convert(varchar(3),@foundresults)

    /* Dynamically grow table */
    IF @foundresults > @maxresults
    BEGIN
    SET @maxresults = @foundresults
    EXEC('ALTER TABLE #working ADD Value' + @ColumnName + ' varchar(50)')
    EXEC('ALTER TABLE #working ADD Desc' + @ColumnName + ' varchar(50)')
    END

    SET @SQL = 'UPDATE #working SET Value' + @ColumnName + '=''' +@Value + ''', Desc' + @ColumnName + '=''' + @Desc + ''''

    EXEC (@SQL)

    SET @LastName = @Name
    FETCH NEXT FROM tabletwo_cursor INTO
    @Name,
    @Value,
    @Desc
    END

    CLOSE tabletwo_cursor
    DEALLOCATE tabletwo_cursor

    SELECT *
    FROM #working

    DROP TABLE #working

    GO

  3. #3
    Join Date
    Dec 2002
    Posts
    45
    The tables are normalized. Table2 can hold many attributes of table1. If a user wants to see all the attributes of table1, we need to retrieve them and display them. Displaying them repeating table1 is not acceptable.
    I also can write it with a cursor (dont need temp table), but can think of many reasons data would need to be returned like this and figured someone would have dealt with this in the past.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    CobaltData, that's too long, and looks very Horacle-ish

    Code:
    create table t1 ([name] varchar(10) not null, garbage1 char(1) not null, garbage2 char(1) not null)
    go
    insert t1
       select 'Gary', 'A', 'B' union
       select 'Mary', 'B', 'A'
    go
    create table t2 ([name]varchar(10) not null, [value] int not null, [desc] varchar(25) not null)
    go
    insert t2 
       select 'Gary', 1, 'Day' union
       select 'Gary', 2, 'Afternoon' union
       select 'Gary', 3, 'Night' union
       select 'Mary', 1, 'Day' union
       select 'Mary', 2, 'Afternoon' union
       select 'Mary', 3, 'Night'
    go
    create function dbo.fn_get_the_rest (
       @name varchar(10) ) returns varchar(100)
    as begin
       declare @retval varchar(100)
       set @retval = ''
       select @retval = @retval + [desc] + ',' + cast([value] as varchar(10)) + ','
    	  from t2 where [name] = @name
       return substring(@retval, 1, datalength(@retval)-1)
    end
    go
    select [name], garbage1, garbage2, [the rest]=dbo.fn_get_the_rest([name]) from t1
    go
    drop function dbo.fn_get_the_rest
    go
    drop table t1, t2
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •