Developer forum

Forum » Development » SQL - Pivot + Inner join?

SQL - Pivot + Inner join?

E-mail notifications
Jacob Storgaard Jensen
Reply
Posted on 27/04/2014 12:59:34

Hi guys,

Might not be the right place to ask this... but here it goes:

I've got some newletter subscribers from Newsletter V3 - which has custom fields. The users and the custom fields are stored in two different tables which i need to display as one via Data Lists... But I can't get my head around Pivoting the one (AccessCustomFieldRow) of them, and join it with the other... + I only want users from the AccessUserGroups with ID=374

Custom fields table:

AccessUser Table (more columns than this shows):

Any ideas on writing the SQL for the data list?


Replies

 
Vladimir
Posted on 27/04/2014 23:58:21
Reply
This post has been marked as an answer

Hi Jacob,

something like that:

SELECT * 
FROM [AccessUser] LEFT JOIN 
(
SELECT * 
FROM 
(
    SELECT [CustomFieldRowFieldID],[CustomFieldRowItemID], cast([CustomFieldRowValue] AS VARCHAR(255)) as FieldValue  
    FROM [AccessCustomFieldRow]
    WHERE [CustomFieldRowItemID] IN (SELECT [AccessUserID] FROM [AccessUser] WHERE [AccessUserGroups] LIKE '%@374@%')
    ) AS t0
pivot (
    MAX([FieldValue]) 
    FOR [CustomFieldRowFieldID] in ([1],[2],[3],[4], [5],[6],[7], [8],[9],[10],[11],[12],[13],[14]) 
    ) AS AvgIncomePerDay
) t1 ON [AccessUserID] = [CustomFieldRowItemID]
WHERE [AccessUserGroups] LIKE '%@374@%'

Best regards,

Vladimir

Votes for this answer: 1
 
Jacob Storgaard Jensen
Posted on 28/04/2014 11:40:45
Reply

Hi Vladimir,

That's almost perfect!

I just tweaked it a bit and almost got what I wanted...

Is there any way to name the "numbered" columns to something else? 2 = Address, 3 = ZipCode etc...

 

 

SELECT AccessUserName, AccessUserEmail, AccessUserMobile, [2],[3],[4],[10],[11],[12],[13],[14]
FROM [AccessUser] LEFT JOIN
(
SELECT *
FROM
(
    SELECT [CustomFieldRowFieldID],[CustomFieldRowItemID], cast([CustomFieldRowValue] AS VARCHAR(255)) as FieldValue
    FROM [AccessCustomFieldRow]
    WHERE [CustomFieldRowItemID] IN (SELECT [AccessUserID] FROM [AccessUser] WHERE [AccessUserGroups] LIKE '%@374@%')
    ) AS t0
pivot (
    MAX([FieldValue])
    FOR [CustomFieldRowFieldID] in ([2],[3],[4],[10],[11],[12],[13],[14])
    ) AS AvgIncomePerDay
) t1 ON [AccessUserID] = [CustomFieldRowItemID]
WHERE [AccessUserGroups] LIKE '%@374@%'
 
Jacob Storgaard Jensen
Posted on 28/04/2014 11:43:20
Reply

Sorry, will answer my own question :-)

If I in the SELECT write:

[2] AS Address, [3] AS ZipCode, etc... then it works

 

You must be logged in to post in the forum