how to make vertical data into horizontal data in sql server?

Farshad Valizade 381 Reputation points
2023-09-30T10:47:31.42+00:00

Hi every body.

I have 3 tables that should be join together and make a horizontal data result.

table form keep form name. each form has many columns and each column has one cell value.I have used this tables to make a form maker app in my project.

please see this tables:

Form Table:

1

Column Table :

2

Cell Table:

3

the result should be something like this:

Capture

and from this result I want to make a join with other tables with FormId key.

what should I do?Please help me.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,911 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-03T20:55:11.6033333+00:00

    nobody can't solve this problem?

    You have been given two solutions. If they are not OK for you, you need to explain why.

    Oh, I see now that you have changed the expected results since I and Cosmog posted our queries.

    If that is the result you want, you should reconsider your table design. What you have is known as EAV, Entity-Attribute-Value. EAV has the advantage of being dynamic. One more attribute - just add it! But there is also distinct disadvantages, and your question is a token of this.

    You are asking for a dynamic pivot, which is a non-relational operation, so you get duly punished when you try this in a relational database. Not that you have to serve time, but the code gets complex.

    As this is a common question, I have written a general text on dynamic pivot which you find here: https://www.sommarskog.se/dynamic_sql.html#pivot. But beware that it is part of a longer article on dynamic SQL, and if you have not worked with dynamic SQL before, you may want to back up to the beginning.

    Tip: you may find it easier to do this operation client-side.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-09-30T16:18:03.3266667+00:00
    SELECT f.FormID, f.FormName AS FormName, C.ColumnID. C.name AS ColumnName, Cl.Celld, Cl.CellValue
    FROM   Forms f
    JOIN   Columns C ON f.FormID = C.FormID
    JOIN   Cells Cl ON C.ColumnID = Cl.ColumnId
    
    0 comments No comments

  2. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2023-10-02T02:01:42.4366667+00:00

    Hi @Farshad Valizade

    A simple join query would work fine, please check Erland's answer.

    and from this result I want to make a join with other tables with FormId key.

    One solution is specifying this temporary named result set, known as a common table expression (CTE). Like this:

    ;WITH CTE AS
    (
     SELECT f.FormID, f.FormName AS FormName, C.ColumnID. C.name AS ColumnName, Cl.Celld, Cl.CellValue
     FROM   Forms f
     JOIN   Columns C ON f.FormID = C.FormID
     JOIN   Cells Cl ON C.ColumnID = Cl.ColumnId
    )
    SELECT ...
    FROM CTE C JOIN Other_Table O ON C.FormID = O.FormID
    

    Also, you could insert the result into a temp table, then query from this temp table join other tables. Use select into.

    SELECT f.FormID, f.FormName AS FormName, C.ColumnID. C.name AS ColumnName, Cl.Celld, Cl.CellValue 
    INTO Temp_Table 
    FROM Forms f JOIN Columns C ON f.FormID = C.FormID              
                 JOIN Cells Cl ON C.ColumnID = Cl.ColumnId
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.