July 31, 2018 at 1:13 pm
Hi there,
I have a problem with eliminating null-values in a cross tab. Perhaps someone out there would be so kind to help me.
The data in the table are as follows:
Create Table tblTest
(
Year int,
Rider nvarchar(10),
Result nvarchar(15)
)
-- Year, Rider, Result (= column names)
Select '1933', 'A', 'Null,' Union All
Select '1933', 'B', 'Null', Union All
Select '1934', 'A', 'Null', Union All
Select '1934', 'B', 'Null', Union All
Select '1935', 'A', '7', Union All
Select '1935', 'B', 'Null', Union All
Select '1936', 'A', '1', Union All
Select '1936', 'B', 'Null', Union All
Select '1937', 'A', '1', Union All
Select '1937', 'B', 'Null', Union All
Select '1938', 'A', '9', Union All
Select '1938', 'B', '1', Union All
Select '1939', 'A', '1', Union All
Select '1939', 'B', '2', Union All
Select '1940', 'A', '9', Union All
Select '1940', 'B', '1', Union All
Select '1946', 'A', '1', Union All
Select '1946', 'B', '2', Union All
Select '1947', 'A', '2', Union All
Select '1947', 'B', '1', Union All
Select '1950', 'A', '2', Union All
Select '1950', 'B', 'No Finish'
The output should be like this:
Year A B (column names)
1935, 7, Null
1936, 1, Null
1937, 1, Null
1938, 9, 1
1939, 1, 2
1940, 9, 1
1946, 1, 2
1947, 2, 1
1950, 2, 'No Finish'
Thus not showing 1933 and 1934 because A and B contain both NULL-values.
My stored procedure looks like this (just to give an idea):
ALTER PROCEDURE dbo.uspBattle
-- Add the parameters for the stored procedure here
@Rennerid1 int,
@Rennerid2 int,
@RennerName1 nvarchar(30),
@RennerName2 nvarchar(30)
AS
BEGIN
DECLARE @Name1 nvarchar(30), @Name2 nvarchar(30), @Id1 int, @Id2 int
SET @Name1 = @RennerName1
SET @Name2 = @RennerName2
SET @Id1 = @Rennerid1
SET @Id2 = @Rennerid2
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
Group By Jaar
Order By Jaar
END
The result of this is always a cross-tab with all the years instead of the years that contain at least one positive value.
Is it possible to create a cross-tab without rows including only NULL-values?
Any help is appreciated.
July 31, 2018 at 1:41 pm
Just filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
END
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 31, 2018 at 2:09 pm
drew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.
July 31, 2018 at 2:16 pm
r_slot - Tuesday, July 31, 2018 2:09 PMdrew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.
To be correct I post the table code once more:
IF OBJECT_ID('tblTest','U') IS NOT NULL
DROP TABLE tblTest
Create Table tblTest
(
Year int,
Rider nvarchar(10),
Result nvarchar(15)
)
-- Year, Rider, Result (= column names)
Select '1933', 'A', Null Union All
Select '1933', 'B', Null Union All
Select '1934', 'A', Null Union All
Select '1934', 'B', Null Union All
Select '1935', 'A', '7' Union All
Select '1935', 'B', Null Union All
Select '1936', 'A', '1' Union All
Select '1936', 'B', Null Union All
Select '1937', 'A', '1' Union All
Select '1937', 'B', Null Union All
Select '1938', 'A', '9' Union All
Select '1938', 'B', '1' Union All
Select '1939', 'A', '1' Union All
Select '1939', 'B', '2' Union All
Select '1940', 'A', '9' Union All
Select '1940', 'B', '1' Union All
Select '1946', 'A', '1' Union All
Select '1946', 'B', '2' Union All
Select '1947', 'A', '2' Union All
Select '1947', 'B', '1' Union All
Select '1950', 'A', '2' Union All
Select '1950', 'B', 'No Finish'
July 31, 2018 at 2:24 pm
r_slot - Tuesday, July 31, 2018 2:09 PMdrew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.
Sorry, I misread the columns, it should be WHERE Result IS NOT NULL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 31, 2018 at 2:32 pm
drew.allen - Tuesday, July 31, 2018 2:24 PMr_slot - Tuesday, July 31, 2018 2:09 PMdrew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.Sorry, I misread the columns, it should be WHERE Result IS NOT NULL.
Drew
Sorry Drew but this is not working either. I agree it should be (I tried it myself) but it does not for some reason. Furthermore we have to keep in mind that one of the columns can be NULL.
July 31, 2018 at 2:43 pm
r_slot - Tuesday, July 31, 2018 2:32 PMdrew.allen - Tuesday, July 31, 2018 2:24 PMr_slot - Tuesday, July 31, 2018 2:09 PMdrew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.Sorry, I misread the columns, it should be WHERE Result IS NOT NULL.
Drew
Sorry Drew but this is not working either. I agree it should be (I tried it myself) but it does not for some reason. Furthermore we have to keep in mind that one of the columns can be NULL.
This works now in the testset:
ALTER PROCEDURE uspTest
-- Add the parameters for the stored procedure here
@RennerName1 nvarchar(10),
@RennerName2 nvarchar(10)
AS
BEGIN
DECLARE @Name1 nvarchar(10), @Name2 nvarchar(10)
SET @Name1 = @RennerName1
SET @Name2 = @RennerName2
Select TOP (100) PERCENT Year,
MAX(Case When Rider = @Name1 Then Result End) As [@Name1],
MAX(Case When Rider = @Name2 Then Result End) As [@Name2]
FROM tblTest
WHERE Result IS NOT NULL
Group By Year
Order By Year
END
And the result:
Year @Name1 @Name2
1935 7 NULL
1936 1 NULL
1937 1 NULL
1938 9 1
1939 1 2
1940 9 1
1946 1 2
1947 2 1
1950 2 No Finish
Unfortunately this is not working with my original code. Any ideas?
July 31, 2018 at 3:02 pm
r_slot - Tuesday, July 31, 2018 2:43 PMr_slot - Tuesday, July 31, 2018 2:32 PMdrew.allen - Tuesday, July 31, 2018 2:24 PMr_slot - Tuesday, July 31, 2018 2:09 PMdrew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.Sorry, I misread the columns, it should be WHERE Result IS NOT NULL.
Drew
Sorry Drew but this is not working either. I agree it should be (I tried it myself) but it does not for some reason. Furthermore we have to keep in mind that one of the columns can be NULL.
This works now in the testset:
ALTER PROCEDURE uspTest
-- Add the parameters for the stored procedure here@RennerName1 nvarchar(10),
@RennerName2 nvarchar(10)AS
BEGIN
DECLARE @Name1 nvarchar(10), @Name2 nvarchar(10)
SET @Name1 = @RennerName1
SET @Name2 = @RennerName2Select TOP (100) PERCENT Year,
MAX(Case When Rider = @Name1 Then Result End) As [@Name1],
MAX(Case When Rider = @Name2 Then Result End) As [@Name2]
FROM tblTest
WHERE Result IS NOT NULL
Group By Year
Order By YearEND
And the result:
Year @Name1 @Name2
1935 7 NULL
1936 1 NULL
1937 1 NULL
1938 9 1
1939 1 2
1940 9 1
1946 1 2
1947 2 1
1950 2 No FinishUnfortunately this is not working with my original code. Any ideas?
Is it also possible to get the Riders' names as column headers? So instead of @Name1 and @Name2 A and B as a header. I thought that @Name1 etc would work but, as you can see, it does not.
July 31, 2018 at 3:09 pm
r_slot - Tuesday, July 31, 2018 2:43 PMr_slot - Tuesday, July 31, 2018 2:32 PMdrew.allen - Tuesday, July 31, 2018 2:24 PMr_slot - Tuesday, July 31, 2018 2:09 PMdrew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.Sorry, I misread the columns, it should be WHERE Result IS NOT NULL.
Drew
Sorry Drew but this is not working either. I agree it should be (I tried it myself) but it does not for some reason. Furthermore we have to keep in mind that one of the columns can be NULL.
This works now in the testset:
ALTER PROCEDURE uspTest
-- Add the parameters for the stored procedure here@RennerName1 nvarchar(10),
@RennerName2 nvarchar(10)AS
BEGIN
DECLARE @Name1 nvarchar(10), @Name2 nvarchar(10)
SET @Name1 = @RennerName1
SET @Name2 = @RennerName2Select TOP (100) PERCENT Year,
MAX(Case When Rider = @Name1 Then Result End) As [@Name1],
MAX(Case When Rider = @Name2 Then Result End) As [@Name2]
FROM tblTest
WHERE Result IS NOT NULL
Group By Year
Order By YearEND
And the result:
Year @Name1 @Name2
1935 7 NULL
1936 1 NULL
1937 1 NULL
1938 9 1
1939 1 2
1940 9 1
1946 1 2
1947 2 1
1950 2 No FinishUnfortunately this is not working with my original code. Any ideas?
Are you sure that they are NULL values rather than strings containing the word NULL?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2018 at 1:12 am
drew.allen - Tuesday, July 31, 2018 3:09 PMr_slot - Tuesday, July 31, 2018 2:43 PMr_slot - Tuesday, July 31, 2018 2:32 PMdrew.allen - Tuesday, July 31, 2018 2:24 PMr_slot - Tuesday, July 31, 2018 2:09 PMdrew.allen - Tuesday, July 31, 2018 1:41 PMJust filter out records with a NULL before doing your crosstab. And do you really have strings that contain the word NULL rather than NULL values?
Select TOP (100) PERCENT Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End) As [@Name1],
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [@Name2]
FROM vwGiroBattle1
WHERE Rennerid <> 'NULL' -- the string 'NULL'
Group By Jaar
Order By Jaar
ENDDrew
Thank you for the reply. You are right we talk about NULL- values not the string 'NULL'.
Your solution (translated by me in WHERE NOT Rennerid IS NULL) does not do the trick. All the rows show up again.Sorry, I misread the columns, it should be WHERE Result IS NOT NULL.
Drew
Sorry Drew but this is not working either. I agree it should be (I tried it myself) but it does not for some reason. Furthermore we have to keep in mind that one of the columns can be NULL.
This works now in the testset:
ALTER PROCEDURE uspTest
-- Add the parameters for the stored procedure here@RennerName1 nvarchar(10),
@RennerName2 nvarchar(10)AS
BEGIN
DECLARE @Name1 nvarchar(10), @Name2 nvarchar(10)
SET @Name1 = @RennerName1
SET @Name2 = @RennerName2Select TOP (100) PERCENT Year,
MAX(Case When Rider = @Name1 Then Result End) As [@Name1],
MAX(Case When Rider = @Name2 Then Result End) As [@Name2]
FROM tblTest
WHERE Result IS NOT NULL
Group By Year
Order By YearEND
And the result:
Year @Name1 @Name2
1935 7 NULL
1936 1 NULL
1937 1 NULL
1938 9 1
1939 1 2
1940 9 1
1946 1 2
1947 2 1
1950 2 No FinishUnfortunately this is not working with my original code. Any ideas?
Are you sure that they are NULL values rather than strings containing the word NULL?
Drew
The solution for me was to:
> first use the select into statement to create a table to put the results in
> second make a cross table to show the results
Like this:
ALTER PROCEDURE dbo.uspBattle
-- Add the parameters for the stored procedure here
@Rennerid1 int,
@Rennerid2 int,
@RennerName1 nvarchar(30),
@RennerName2 nvarchar(30)
AS
BEGIN
DECLARE @Name1 nvarchar(30), @Name2 nvarchar(30), @Id1 int, @Id2 int
SET @Name1 = @RennerName1
SET @Name2 = @RennerName2
SET @Id1 = @Rennerid1
SET @Id2 = @Rennerid2
IF OBJECT_ID('myTable','U') IS Not NULL
DROP TABLE myTable
Select TOP (100) PERCENT Jaar, Resultaat, Achternaam, Rennerid
INTO myTable
FROM vwGiroBattle2
WHERE Rennerid=@Id1 OR Rennerid=@Id2
Order By Jaar
Select Jaar,
MAX(Case When Rennerid = @Id1 Then Resultaat End),
MAX(Case When Rennerid = @Id2 Then Resultaat End) As [Achternaam2]
FROM myTable
GROUP BY Jaar
ORDER BY Jaar
END
The results look like this:
Jaar (No column name) Achternaam2
1935 7/00,16,01 NULL
1936 1/00,00,00 NULL
1937 1/00,00,00 NULL
1939 2/00,02,59 NULL
1940 9/00,46,09 1/00,00,00
1946 1/00,00,00 2/00,00,47
1947 2/00,01,43 1/00,00,00
1948 8/00,11,52 Protestafstap/Etappe Onb
1949 2/00,23,47 1/00,00,00
1950 2/00,05,12 Opgegeven/Etappe Onb
1951 10/00,21,12 4/00,04,04
1952 5/00,10,33 1/00,00,00
1953 4/00,14,08 1/00,00,00
1954 13/00,50,11 4/00,31,17
1955 NULL 2/00,00,13
1956 NULL Gevallen/05e etappe
1958 NULL 32/00,56,14
The above is based on the real situation. One problem remains: is it possible to code the headers with the name of the riders. The As clause does not give any clue. Any ideas?
Thanks for all the help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply