SQL Server Performance

Complex Query

Discussion in 'General Developer Questions' started by danny123, Sep 15, 2008.

  1. danny123 New Member

    Hi there,
    We are using a internal application to create the forms. Form creater have provided with form fields which he can drag and drop into forms.
    There are 3 separtate tables tblForms,tblFields and mapping table tblFormFields.
    Now i want all the information in one row about one form so that Field label become the header of result and field value become the rows.
    Can someone please give some headsup.
    Thanks!!
  2. Adriaan New Member

    Look up the SELECT syntax in Books Online, and pay special attention to the part about JOIN.
    This is basic T-SQL syntax - learn it first, then if you run into problems in practice you can come here and ask questions.
  3. danny123 New Member

  4. danny123 New Member

    I guess i did not explain it well.
    If i use simple select statement using joins i will get multiple rows ( as many as there are fields) but i want all the result in one row.
    for example :
    tblForms :
    FormID FormName
    1 form1
    2 form2
    3 form3
    tblFields :
    FieldID FieldLabel FieldValue
    1 FL1 FV1
    2 FL2 FV2
    3 FL3 FV3
    tblFormToFields:
    FormID FieldID
    1 1
    1 2
    2 2
    3 1
    3 2
    3 3
    Now result should be in one row with all the details for one form like field label become the column name and field value become the actual row value.
    Now i want the result to look like this for FormID = 1
    FormID FL1 FL2 ----------------> Column Names
    1 FV1 FV2 -----------------> Single row with values
    Thanks!
  5. Adriaan New Member

    Do a search on cross-tab queries.
    If you've posted in the SQL 7.0/2000 area only by accident, and you're actually working in SQL 2005, then check out the PIVOT syntax.
    In any case, does the information really have to be presented with all the field names side-by-side? You could also let the client application do this by looping through the rows of your result set.
  6. danny123 New Member

    Thanks for your support.
    I got it working using cursors and dynamic sql.
    Thanks!
  7. Madhivanan Moderator

    [quote user="danny123"]
    Thanks for your support.
    I got it working using cursors and dynamic sql.
    Thanks!
    [/quote]
    Can you post the code you used?

Share This Page