Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS TSQL Select Question combine / merge NULL Values

Posted on 2013-06-06
19
Medium Priority
?
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 2
  • +1
19 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 39225610
Hi HelpdeskJBC,

So what is the expected output?
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 49

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 49

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 49

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
 
LVL 49

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 49

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 49

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 49

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 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 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