At first let me explain you my DB construct.
I use MSSQL with the sqlsrv driver of php.
I have two DB tables Names and AccountFieldValues.
Names has a variable field NameID and a fix field StringValue.
This table contains all names of the accountfield variables ergo: FirstName,LastName etc.
AccountFieldValues has the same variable field FieldNameID,FieldValue and IdentityID.
This table contains the content of the accountfield variables for every user.
Now to get a specific variable content of the user with the IdentityID=10 and the StringValue='FirstName' you can create a query like that:
SELECT FieldValue FROM AccountFieldValues
WHERE IdentityID=10 AND FieldNameID IN (SELECT NameID FROM Names WHERE StringValue='FirstName')
Or with a inner join
SELECT ACF.FieldValue FROM AccountFieldValues ACF
INNER JOIN Names N ON (N.StringValue='FirstName' AND N.NameID=ACF.FieldNameID)
WHERE ACF.IdentityID=10
When you want a single variable content both methods work well.
But now comes the problem, when i want to get multiple variable contents in one query this gets tricky.
I can expand the two queries like this:
SELECT FieldValue FROM AccountFieldValues
WHERE IdentityID=10 AND FieldNameID IN
(SELECT NameID FROM Names WHERE StringValue='FirstName' OR StringValue='LastName')
And the other query like this:
SELECT ACF.FieldValue AS 'FirstName', ACF2.FieldValue AS 'LastName' FROM AccountFieldValues ACF
INNER JOIN Names N ON (N.StringValue='FirstName' AND N.NameID=ACF.FieldNameID)
INNER JOIN AccountFieldValues ACF2 ON (ACF2.IdentityID=ACF.IdentityID)
INNER JOIN Names N2 ON (N2.StringValue='LastName' AND N2.NameID=ACF2.FieldNameID)
WHERE ACF.IdentityID=10
Although ugly as hell in comparison with the upper method this will give me the result of FirstName and LastName in one Result Row. like this:
FirstName|LastName
------------------
Foo |Bar
On the other hand the first Method will give me a result looking like this:
FieldValue
----------
Foo
Bar
This isn't desirable because i cannot fetch the wanted AccountFieldValues for every user in one resultset
Is there a method to get the result of the tables like with the ugly joins but faster and easily expandable like the first method?
What you're looking for is a way to pivot your data. Here is one using conditional aggregation:
SELECT
FirstName = MAX(CASE WHEN n.StringValue = 'FirstName' THEN acf.FieldValue END),
LastName = MAX(CASE WHEN n.StringValue = 'LastName' THEN acf.FieldValue END)
FROM AccountFieldValues acf
INNER JOIN Names n
ON n.NameId = acf.FieldNameId
WHERE acf.IdentityId = 10
GROUP BY acf.IdentityId
This is a not dynamic. You can also remove the JOIN on Names and use the acf.FieldId in your CASE instead. Like this:
MAX(CASE WHEN acf.FieldId = <FIELD_ID_OF_FIRSTNAME> THEN acf.FieldValue END)
In case you want a dynamic approach, here is one way using dynamic sql:
DECLARE @sql1 NVARCHAR(MAX) = '',
@sql2 NVARCHAR(MAX) = '',
@sql3 NVARCHAR(MAX) = ''
DECLARE @identityId INT = 10
SELECT @sql1 =
'SELECT
acf.IdentityId' + CHAR(10)
SELECT @sql2 = @sql2 +
' , MAX(CASE WHEN acf.FieldNameId = ' + CONVERT(VARCHAR(10), t.FieldNameId) + ' THEN acf.FieldValue END) AS '
+ QUOTENAME(n.StringValue) + CHAR(10)
FROM(
SELECT DISTINCT FieldNameId FROM AccountFieldValues WHERE IdentityId = @identityId
)t
INNER JOIN Names n ON t.FieldNameId = n.NameId
SELECT @sql3 =
'FROM AccountFieldValues acf
INNER JOIN Names n
ON n.NameId = acf.FieldNameId
WHERE acf.IdentityId = @identityId
GROUP BY acf.IdentityId'
DECLARE @finalSql NVARCHAR(MAX) = ''
SET @finalSql = @sql1 + @sql2 + @sql3
EXEC sp_executesql @finalSql, N'@identityId INT', @identityId