empty fields in a query

60 pts.
Tags:
CASE statement
NULL
SQL 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)

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • msi77
    What result set do you expect to get?
    1,660 pointsBadges:
    report
  • Bananahead
    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.
    60 pointsBadges:
    report
  • Kccrosser
    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,
    3,830 pointsBadges:
    report
  • Bananahead
    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.
    60 pointsBadges:
    report
  • Bananahead
    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.
    60 pointsBadges:
    report
  • Darryn
    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
    765 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following