Solved

MS TSQL Select Question combine / merge NULL Values

Posted on 2013-06-06
19
525 Views
Last Modified: 2013-06-13
Hello
We are using MS SQL 2008R2

my current Table looks like this:

Date  Name  KW1  KW2   KW3 .... KW52
xz     uy          NULL    29.4   34          NULL
xy      xy         NULL  NULL  4          13.8
xy      xy         NULL  NULL  NULL      13.8
xz     uy          13.5  29.4   34          NULL

So I've got 52 columns (all weeks of a year) but for each row there are only 8 Values of Data, this means: 8 Values with data and 44 values with NULL in each row.
I would now like to combine the columns to get only 8 of them and no NULL values in my select statement.


PS
The Table above is not the source its the result of a pivot select statement

select top 10 * from
(select [Date], Name, datepart(wk,StartDate)+1-datepart(wk,'Jan 04,'+CAST(datepart(yy,StartDate) as CHAR(4))) as KW, Price from Tablex
) p
pivot
(
min (Price)
FOR KW IN
(
[1], [2], [3], [4], .... [49], [50], [51], [52]
)
) AS pvt order by [Date] desc

Is there a way to combine the result?
0
Comment
Question by:HelpdeskJBC
  • 10
  • 6
  • 2
  • +1
19 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 39225610
Hi HelpdeskJBC,

So what is the expected output?
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39227212
0
 

Author Comment

by:HelpdeskJBC
ID: 39228112
Output should combine the values in 8 rows. Some kind of merge the columns or don't show NULL values in the result.

There are always only 8 values out of this 52 weeks and they are moving from each day so if the first date is 1.1 the values start with kw1 (first week to week 8) on the date 31.12 the values of kw1 to kw 51 are null.

The result should not include the column names kw1 2 3 4 ... it should look like Column 1-8
so the data on 31.12 should not show up as NULL NULL NULL NULL NULL 1 2 3 4 5

it should only uses 8 rows and start directly with the date without showing NULL values
Simply all Data should move to the left and after column 8 is a cut of
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 39228215
When you say "combine", what do you mean by that? Summed? Concatenated into a string? Some example would be helpful.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228259
I interpret to mean you still want 'per week' columns, but only 8 of them

If so, why select 52 weeks, if only 8 will contain data?
i.e. can  you just include a date range filter?

are you producing this using dynamic sql, or is the query static?
0
 

Author Comment

by:HelpdeskJBC
ID: 39228419
There are only 8 values on 52 columns but I can't just select 8 because they are drifting trough the rows.

They start on the first row like 1 2 3 4 5 6 7 8 NULL NULL NULL  NULL NULL
Second Row                                NULL 1 2 3 4 5 6 7 8 NULL NULL NULL
Third Row                                   NULL NULL 1 2 3 4 5 6 7 8 NULL NULL

etc.

so this Data is drifting away to the right side.
I don't need 52 columns I would like to move all the data to the left side and don't show the NULL values.

I use this as VBA parameter in Excel the current Result is attached
The main Question to handle is to how can I put this result into a table with only 8 rows this means move the data values to the left and delete the empty cells
data.jpg
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228436
>>I think we can ascertain what you want as output (8 non-null columns)
thanks, output is clearer now, but still a few unanswered questions (from all here)

What version of SQL Server are you running?
(this always very very useful to know as options differ over versions)

are you producing this using dynamic sql, or is the query static?

and a new request:
can you provide some sample data please?
0
 

Author Comment

by:HelpdeskJBC
ID: 39228472
We are using MS SQL 2008 R2 (first post second line)
query is static only "top 100" value will change

Sample data attached

as mentioned above the KW Column is created with my formula and after that the pivot table arrange the values as you can see in the excel output.
sample.txt
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228474
sorry, but I really cannot envision the source data that meets this 8 week drift against the query you have supplied...
the query has rows by [Date] and pivoted by a week reference
- I would expect that to produce just one data point per row.

Is [Date] actually some form of "duration" ?
and/or
Have you "trimmed down" the query at all?

apologies for the 20 questions - but I'm struggling with question right now.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228488
>>first post second line
oh dear - most sorry

thanks for the data
0
 

Author Comment

by:HelpdeskJBC
ID: 39228515
Sorry the sample was not the best file i attached a new one

First Column is the Input Date
As you can see on the same input date we get values for 7 weeks (period start end)
This Period is moving deepening on the import date so this is why i have this flow to the right.
sample.txt
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228553
OK, I'll look at the newer data, but I think my conclusions will be the same.

As I 'envisioned', because each row is a date, then the data point can only be on ONE column (not 8). Hence there is either a rather big piece of the puzzle missing, or I would suggest you stop pushing the data into weekly columns. Perhaps just use Day-Of-Week like this (i.e. a maximum of 7 columns, only 5 of which are probably used.
SELECT
*
FROM (
	SELECT [Date]
		, NAME
		, datepart(dw, StartDate) AS KW
		, Price
	FROM TableX
	) p
pivot(min(Price) FOR KW IN (
                              [1]
                            , [2]
                            , [3]
                            , [4]
                            , [5]
                            , [6]
                            , [7]
                            )
     ) AS pvt
ORDER BY [Date] DESC

Open in new window

I don't wish to annoy you - but have you provided the complete query that you use? I cannot replicate data in 8 columns, and whilst I'd really like to help further until I can understand how you get 8 non-null columns - I simply don't know what to offer.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228581
That newer data has helped in my understanding.... I can see now how data is being placed into multiple columns per InputDate (as one InputDate relates to multiple StartDates).

So the day-of-week idea is probably out the door. I'll be back with something else I hope.
0
 

Author Comment

by:HelpdeskJBC
ID: 39228671
during the result is in excel there is the potability to use vba macros for the final arrangement.
What I do now is this simple code:
 Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlToLeft
This is exactly what i was looking in SQL
Its very fast and simple

This is also the final solution but if you have any ideas to manage everything on server side this would be great.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228713
I have an idea, but you will need to test it, and you must also decide if it is easier/faster than the VBA approach. (If you have something that works, maybe you should stick with it?).

Anyway, it seems to me that you need to stop using calendar weeks as the determinant of columns. Instead, calculate a week number for each StartDate (within an InputDate) that will fall into the range 1-8. The following (I hope) does this and hopefully the structure of the query below will be sufficiently notated for you to follow. The idea is that you filter the source data in the top "common table expression" (CTE) , then a second CTE calculates an "offset number" that is applied to the calendar week of each StartDate. The result "should be" week numbers that align to columns 1 through 8.
;WITH
CTE1 AS (
                /* CTE1 should be used to gather the data for consideration
                   so that the where clause occurs once
                */
            SELECT
              name
            , InputDate
            , StartDate
            , price
            FROM SourceTable
            /* i.e. do all filtering here */
            WHERE 1=1
          )
, CTE2 AS (
                /* CTE2 calculates an "offset" to deduct for each InputDate
                   for the week numbers that each StartDate would provide
                   so that after the deduction all "weeks" will placed
                   into common columns (only 8 are expected)
                */
            SELECT
               InputDate
             , datepart(week,min(StartDate)) - 1  WkOffset
            FROM CTE1
            GROUP BY
               InputDate
          )
SELECT
*
FROM (
    SELECT
          CTE1.InputDate
        , CTE1.NAME
        , datepart(week,CTE1.StartDate) - CTE2.WkOffset AS KW
        , CTE1.Price
    FROM CTE1
    INNER JOIN CTE2 ON CTE1.InputDate = CTE2.InputDate
    ) p
PIVOT (min(Price) FOR KW IN (
                              [1]
                            , [2]
                            , [3]
                            , [4]
                            , [5]
                            , [6]
                            , [7]
                            , [8]
                            )
     ) AS pvt
ORDER BY InputDate DESC

Open in new window

I have this working on very limited data (a sqlfiddle limitation) here: http://sqlfiddle.com/#!3/225bc/14

Testing a larger scale will be essential. My major concern is that data could spread beyond the 8 allowed columns.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39231186
this is a more reliable method of calculating that offset, use this in preference to the above
;WITH
CTE1 AS (
                /* CTE1 should be used to gather the data for consideration
                   so that the where clause occurs once
                */
            SELECT
              name
            , InputDate
            , StartDate
            , price
            FROM SourceTable
            /* i.e. do all filtering here */
            WHERE 1=1
          )
, CTE2 AS (
                /* CTE2 calculates an "offset" to deduct for each InputDate
                   for the week numbers that each StartDate would provide
                   so that after the deduction all "weeks" will placed
                   into common columns (only 8 are expected)
                */
            SELECT
               InputDate
             , datediff(week,0,min(StartDate)) - 1  WkOffset
            FROM CTE1
            GROUP BY
               InputDate
          )
SELECT
*
FROM (
    SELECT
          CTE1.InputDate
        , CTE1.NAME
		, datediff(week,0,CTE1.StartDate) - CTE2.WkOffset AS KW
        , CTE1.Price
    FROM CTE1
    INNER JOIN CTE2 ON CTE1.InputDate = CTE2.InputDate
    ) p
PIVOT (min(Price) FOR KW IN (
                              [1]
                            , [2]
                            , [3]
                            , [4]
                            , [5]
                            , [6]
                            , [7]
                            , [8]
                            )
     ) AS pvt
ORDER BY InputDate DESC

Open in new window

http://sqlfiddle.com/#!3/2747e/4
0
 

Author Comment

by:HelpdeskJBC
ID: 39244251
I've requested that this question be closed as follows:

Accepted answer: 0 points for HelpdeskJBC's comment #a39228671

for the following reason:

Easy way
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39236082
I provided a solution, why should this be unrewarded?

The questions was:
Is there a way to combine the result?

which I provided...

The VBA alternative has been chosen in preference - that doesn't mean the question hasn't been answered.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39244282
thanks, sorry the solution didn't get the preference, but one can only do so much in sql.

Cheers, Paul
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now