CODEWITHSUNDEEP
sqlsql-serverpivot
keyur ajmera
keyur ajmera

Reputation: 63

SQL Server JOIN Database like wordpress

I need to make generic database. It should looks like below example.

http://s11.postimage.org/4n6sigf37/Untitled_picture.png

I need some dynamic store procedure or query.

It's kind of wordpress post storing in my sql.

I need to do with SQL Server.

Upvotes: 0

Views: 117

Answers (2)

Taryn
Taryn

Reputation: 247850

Basically, you are trying to do a PIVOT of the data where you take the values in the key column and transform each of those into its own column. If you know the values that you want to transform, then you can hard-code them via a static PIVOT, similar to this:

select id, name, email, password, state, city
from 
(
  select u.id,
    u.name,
    u.email,
    u.password,
    m.[key],
    m.value
  from users u
  left join usermeta m
    on u.id = m.user_id
) x
pivot
(
  max(value)
  for [key] in ([State], [City])
) p;

See SQL Fiddle with Demo

But you stated that you have an unknown number of columns, so you will want to use a dynamic PIVOT:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME([key]) 
                    from usermeta
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, name, email, password, ' + @cols + ' from 
             (
                select u.id,
                  u.name,
                  u.email,
                  u.password,
                  m.[key],
                  m.value
                from users u
                left join usermeta m
                  on u.id = m.user_id
            ) x
            pivot 
            (
                max(value)
                for [key] in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Upvotes: 2

Kermit
Kermit

Reputation: 34063

This should work:

SELECT a.ID, a.Name, a.Email, a.Password
    (SELECT Value FROM UserMeta WHERE Key = 'State' AND User_Id = a.ID) AS State,
    (SELECT Value FROM UserMeta WHERE Key = 'City' AND User_Id = a.ID) AS City
FROM User a

Upvotes: 0

Related Questions