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

Question

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:

Column Table :

Cell Table:

the result should be something like this:

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

what should I do?Please help me.

Answer 1

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.

Answer 2

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

Answer 3

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.