SQL Server Performance

Creating table fields that link from the records

Discussion in 'SQL Server 2008 General DBA Questions' started by andrern2000, Jun 26, 2009.

  1. andrern2000 New Member

    I'm running on Sybase and need a help on SQL Query.
    I have, say, 50 records with 10 distinct dates: April-10-2009 s/d Apr-20-2009. I want to summarize the records into a table, with each distinct node record as the name of fields. Why so that if new distinct node is added, they also update, adding the newly added node record as new fields.
    Technically, each distinct date contains information of 5 nodes with an node output amount. I must create a table(or view, whatever is ok) that contains fields whose name that comes from the names of 5 nodes. The records beneath the field header are the node output amount for each distinct dates.
    So it looks like this in the final:
    Date | TSNode1 | TSNode2 | TS Node3 | TSNode4 | TSNode5 | --> (These are the records from the other table, so when new records added, these fields
    Apr-10 9,875 8,657 5,433 6,789 5,534 also update, adding new fields here)
    Apr-11 8,975 10,635 9,453 8,139 6,179
    Apr-12 ... ... ... ... ...
    Apr-13 ... ... ... ... ...
    Apr-14 ... ... ... ... ...
    ...
    Apr-20
    Is it possible to do this?
  2. FrankKalis Moderator

    Welcome to the forum!
    This is mainly an SQL Server community, so I`m not entirely sure if this is possible in Sybase at all. You're looking for a crosstab query like so:
    DECLARE @t TABLE (thedate smalldatetime, someotherattribute int, node int)
    INSERT INTO @t
    SELECT '2009-04-10', 1, 9875 UNION ALL
    SELECT '2009-04-10', 2, 8657 UNION ALL
    SELECT '2009-04-11', 1, 8975 UNION ALL
    SELECT '2009-04-11', 2, 10635

    SELECT
    T1.thedate,
    MIN (CASE WHEN T1.someotherattribute = 1 THEN T1.node ELSE NULL END) AS TSNode1,
    MIN (CASE WHEN T1.someotherattribute = 2 THEN T1.node ELSE NULL END) AS TSNode2
    FROM
    @t T1
    GROUP BY
    T1.thedate
    2009-04-10 00:00:00 9875 8657
    2009-04-11 00:00:00 8975 10635

  3. andrern2000 New Member

    Thankyou! :D
    Thanks, but apparently that's not the case I'm expecting.
    So what I expect is how to create a table that has fields coming from the records from the previous table. These fields automatically pulls out the data from appropriate records in the previous table, not by filling it manually via query. And when a new kind of distinctive record is added in the previous table, that new kind is placed as the fields as well.
    In the above example, the distinctive records from previous table are TSNode1-5.
    Once again thanks for the rply. I think I must change the thread's title so ppl don't misundertand it.
    PS: For syntax example, use [PrevTable] as previous table, and [NewTable] as the new table.
  4. andrern2000 New Member

    Would Inner Join solve this problem?
  5. Adriaan New Member

    Sounds to me as if you'll need to create a temp table, then loop through a cursor on the source data and use ALTER TABLE on the temp table to add columns as required, and set the column values as required. You will probably need to use dynamic SQL for this.
    You can then return the temp table as the result of a stored procedure.
  6. andrern2000 New Member

    k, I'll try that. Thanks.
  7. andrern2000 New Member

    Ok, so I have solved this using my own way. I don't create fields from the records. Thanks alot btw,

Share This Page