empty fields in a query
I have a column with answers that people responded to different questions about themseleves. I have another column with the question id that the answer corresponds to. i want to display the answers in separate columns based on which question it corresponds to. Each column should be filled with the answers to the specific question. I used a case statement to divide the answer column into different columns based on their question id. The problem is that i don't know what to put after the else. if i put Null, then i have a lot of empty fields that say null. I want that the fields shouldn't show up all together.
Here is my query:
SELECT DISTINCT
TOP (100) PERCENT dbo.ProfileAnswers.u_username AS Username, CASE ProfileAnswers.pq_id WHEN 101 THEN ProfileAnswers.pa_value ELSE NULL
END AS HebrewName, CASE ProfileAnswers.pq_id WHEN 42 THEN ProfileAnswers.pa_value ELSE NULL END AS 'Learing/Working',
CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE NULL END AS 'Previously Married',
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE NULL END AS Type, dbo.ProfileAnswers.pa_approved AS Approved,
DATEDIFF(Year, dbo.Users.u_birthdate, GETDATE()) AS Age
FROM dbo.ProfileAnswers INNER JOIN
dbo.Users ON dbo.ProfileAnswers.u_username = dbo.Users.u_username
WHERE (dbo.Users.u_gender = 2)
Software/Hardware used:
Software/Hardware used:
ASKED:
May 5, 2009 4:20 PM
UPDATED:
May 8, 2009 5:29 PM
Answer Wiki:
Bananahead - I would begin each field's select statment with "is not null" then as you have it. Naturally delete the final "else"
Good luck!
JuJu
On second look, I think just deleting "else Null" will give you the results you want. The "else Null" is telling the program to enter the word "Null" when the other conditions are not met. Let me know if this helps.
JuJu
To see all answers submitted to the Answer Wiki: View Answer History.
What result set do you expect to get?
In the column that asks for name, i want name only, age i want age only. Here i get name and whefe there is information filled into the other fields i get NULL. i want to get rid of that all together. yoiu shouldn’t know there was something in the database there.
I am not sure I understand your objectives, but is it as simple as replacing the “NULL” with an empty string? Like:
CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE ” END AS ‘Previously Married’,
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE ” END AS Type,
Or use a string with a blank, to avoid the empty string being interpreted as null:
CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE ‘ ‘ END AS ‘Previously Married’,
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE ‘ ‘ END AS Type,
that wont help. the database is not empty. its showing null for all the answers that i don’t want to see in this colum. for example, lets say i want to show the age for each person in my database. it will show the name, the age and blank spaces for each of the other questions that i don’t want to show.
I tried IS NOT NULL but that doesn’t work because the database is not null. I told it to put in Null or an empty string instead of what is in that field in the database. I need that instead of showing these things, the field should not show up at all. Since all the answers are in the same column, i am having a hard time splitting this one column into a couple of different columns.
This is not ideal, but it should work:
select username,
max(HebrewName) HebrewName,
max(Learning_Working) Learning_Working,
max(Previously_Married) Previously_Married,
max(Type) Type
max(Approved) Approved
from
(
SELECT DISTINCT
TOP (100) PERCENT dbo.ProfileAnswers.u_username AS Username, CASE ProfileAnswers.pq_id WHEN 101 THEN ProfileAnswers.pa_value ELSE NULL END AS HebrewName,
CASE ProfileAnswers.pq_id WHEN 42 THEN ProfileAnswers.pa_value ELSE NULL END AS ‘Learing_Working’,
CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE NULL END AS ‘Previously_Married’,
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE NULL END AS Type, dbo.ProfileAnswers.pa_approved AS Approved,
DATEDIFF(Year, dbo.Users.u_birthdate, GETDATE()) AS Age
FROM dbo.ProfileAnswers INNER JOIN
dbo.Users ON dbo.ProfileAnswers.u_username = dbo.Users.u_username
WHERE (dbo.Users.u_gender = 2) )
group by username