Is there a way to make this run without a case statement?

Question

I'm relatively new to coding and SQL so please bear with me.

I'm currently working on a query and I have no idea how to get the infinite loop to stop without using a case statement. When I use the case statement I get each value on its own row rather than the values all together in the combination they're supposed to be in.

Case statement SQL

select
    CASE
        When Attribute_id = '5024923' Then attribute_value
    END Page_Name,
    CASE
        When Attribute_id = '5024925' Then attribute_value
    END Site_Name,
    CASE
        When Attribute_id = '5024924' Then attribute_value
    END Last_Touch_Channel,
    count(distinct MASTER_CONTACT_ID) known_contact_count,
    count (distinct visitor_id) total_contact_Count,
    ACTION_DATE
    from Adobe_Analytics_Staging
    where ATTRIBUTE_ID in ('5024925','5024924','5024923')
    group by ATTRIBUTE_ID, ACTION_DATE, ATTRIBUTE_VALUE

Example: Error with Case statement:

Column A	Column B	Column C
value1	NULL	NULL
NULL	value2	NULL
NULL	NULL	value3

When in the data it is value1, value2, value3 on the same row.

So I'm trying a new avenue. I suspect the loop is because I'm linking back to the table so many times but I have limited the amount of results to the best of my ability to reduce the amount of records being sent through. Each query works and works fast individually. It's collectively that it slows down a ton.

The reason for joining to the table so many times is because I have to distinguish different types of values within one column.

Note: Not sure if it's relevant but the different values in the table correlate to a specific id number within that that table. Attribute value and attribute ID are different columns

For example in Table A the column looks like this

Column
A
B
C

I have to make it look like this:

Column 1	Column 2	Column 3
A	B	C

select 
a.ATTRIBUTE_VALUE,
b.ATTRIBUTE_VALUE,
c.ATTRIBUTE_VALUE,
count(distinct aas.MASTER_CONTACT_ID) known_contact_count,
count (distinct d.visitor_id) total_contact_Count,
aas.ACTION_DATE
from Adobe_Analytics_Staging aas
        left join (select ATTRIBUTE_VALUE, VISITOR_ID from Adobe_Analytics_Staging
        where Attribute_id = '5024923') a on a.VISITOR_ID = aas.VISITOR_ID

        left join (select ATTRIBUTE_VALUE, VISITOR_ID  from Adobe_Analytics_Staging
        where Attribute_id = '5024925') b on b.VISITOR_ID = aas.VISITOR_ID

        left join (select ATTRIBUTE_VALUE, VISITOR_ID  from Adobe_Analytics_Staging
        where Attribute_id = '5024924') c on c.VISITOR_ID = aas.VISITOR_ID

        inner join (select visitor_id from Adobe_Analytics_Staging
                    where ATTRIBUTE_ID in ('5024923','5024925','5024924')) d
                    on d.VISITOR_ID = aas.VISITOR_ID
--where aas.VISITOR_ID = '3438634761938550664_6795123974460253552'
group by a.ATTRIBUTE_VALUE, b.ATTRIBUTE_VALUE, c.ATTRIBUTE_VALUE, aas.ACTION_DATE

Is there a reason why you want to avoid [`CASE WHEN THEN END AS`](https://stackoverflow.com/questions/26185376/sql-case-when-then-as)? — surfmuggle, Oct 12 '21 at 17:41
is there a (good) reason why you obfuscated (most of, but not all of) the table names, and left some references to the table `aas` which is not selected, and also a bad typo here: `from Table A top`.... Also: Why are the left joins not filtered on `ACTION_DATE`, this field is used in grouping..... — Luuk, Oct 12 '21 at 17:47
@Luuk There's no real reason I just wasn't sure if that was the protocol for the site. Previous times I've been on here, that's what I've seen. I could just post the straight sql if that will help. — BeeBoop, Oct 12 '21 at 17:52
Yes it would help, as would proper sample data and expected results, at the moment it's unclear what exact results you want. Probably conditional aggregation would be a better option — Charlieface, Oct 12 '21 at 17:54
@surfmuggle The Case statement shows the individual values on a row by itself rather in the combination that it shows in the data. If the data point has an page, site and channel value in the same row. The case statement I currently have gives each value a different row. I can post the Case Statement SQL if that would help — BeeBoop, Oct 12 '21 at 17:54
@BeeBoop: The most important thing is that the statement is without syntax errors, unless you have a really weird problem with the syntax... (but then you are expected to give the full error message) — Luuk, Oct 12 '21 at 17:59
@Luuk are you referring to the case statement or the self join one? — BeeBoop, Oct 12 '21 at 18:05
The schema design here looks a lot like **Entity-Attribute-Value**, which is considered an anti-pattern in most cases... that is, something to be avoided. What you're seeing here is one reason (of several) why. The fix (if you are unable to fix the schema) is you must **`JOIN`** the table to itself, so you have three separate instances of the same table, one for each of the desired columns. — Joel Coehoorn, Oct 12 '21 at 20:06
You want a [pivot](https://en.m.wikipedia.org/wiki/Pivot_table). — Bohemian, Oct 12 '21 at 20:16

score 1 · Answer 1 · edited Oct 12 '21 at 20:43

SELECT
    VISITOR_ID,  
    MAX(CASE WHEN Attribute_id = '5024923' Then attribute_value END) Page_Name,
    MAX(CASE WHEN Attribute_id = '5024925' Then attribute_value END) Site_Name,
    MAX(CASE WHEN Attribute_id = '5024924' Then attribute_value END) Last_Touch_Channel,
    COUNT(distinct MASTER_CONTACT_ID) known_contact_count,
    COUNT(distinct visitor_id) total_contact_Count,
    ACTION_DATE
FROM ContactTargeting.dbo.Adobe_Analytics_Staging
GROUP BY VISITOR_ID, ACTION_DATE

See this fiddle with some demo data

Is there a way to make this run without a case statement?

1 Answers1