SQL Server Performance

Help with cross tab query

Discussion in 'General Developer Questions' started by vimalpercy, Apr 13, 2010.

  1. vimalpercy New Member

    Hello Experts
    I have a view with data as shown below.
    Col1 Col2 Col3 Col4
    abc NULL NULL NULL
    NULL def NULL NULL
    ghi NULL NULL NULL
    NULL sql NULL NULL
    NULL NULL xsls NULL
    NULL NULL NULL you
    What I want is like this
    abc def xsls You
    ghi sql --- ---
    Remove Null Items from each column and arrange them side by side.
    Thanks in advance for the quick reply
  2. Adriaan New Member

    Your table design is quite probably not optimized for the information that goes in there. Find the right design through normalization, so you can avoid these presentation issues.
  3. vimalpercy New Member

    Thanks Adriaan for the reply
    This is just a form of cross tab view I obtained from a table with 4 different categories (4 columns)
    Now I need to present the 4 items side by side as 4 independent items.
    Is it possible to do it with SQL or do I need to create a temp table and do some processing?
  4. Adriaan New Member

    You seem to start a series where col1 is not null, then progress to the next row where col2 is not null, then for col3 and col4. Then from your previous starting point, you find the next row where col1 is not null, and take it up from there again (hopefully skipping the rows that you've already read from).
    That looks like something you could do in a CURSOR based loop.
    My main concern is how you're ordering/grouping this (presumably a couple of key columns you're not showing us) - also, if there is a grouping, how you would handle multiple entries under the same category.
  5. vimalpercy New Member

    I have a table say ID, FieldName
    I am breaking this into a cross tab by 4 categories using a Case Statement
    ID FieldName1,Fieldname2,FieldName3,FieldName4
    Eventually what I get is list of all records from table each row representing any one of the 4 columns.
    I want them to be represented as independent list side by side.
  6. Adriaan New Member

    So you have an over-normalized table, where instead of columns you have "repeating rows".
    Try derived tables ...
    SELECT COALESCE(Column1.ID, Column2.ID, Column3.ID, Column4.ID) AS ID, Column1.Value AS FieldName1, Column2.Value AS FieldName2, Column3.Value AS FieldName3, Column4.Value AS FieldName4
    FROM
    (SELECT Id, Value FROM Table
    WHERE FieldName = 'FieldName1') AS Column1
    FULL JOIN
    (SELECT Id, Value FROM Table
    WHERE FieldName = 'FieldName2') AS Column2 ON Column1.ID = Column2.ID
    FULL JOIN
    (SELECT Id, Value FROM Table
    WHERE FieldName = 'FieldName3') AS Column3 ON COLUMN1.ID = Column3.ID
    FULL JOIN
    (SELECT Id, Value FROM Table
    WHERE FieldName = 'FieldName4') AS Column4 ON COLUMN1.ID = Column4.ID
  7. vimalpercy New Member

    Hello Adriaan
    Thanks for the reply. The following script provides me the cross tab view of the table.
    But I want to remove the null values in each column and group them together like
    Col1ID Column1 Col2ID Column2 Col3ID Column3 Col4ID Column4.

Share This Page