October 11, 2011 at 10:42 am
I am doing pivot on my result set to get in a desired way but I am facing some issues.
I am getting NULL for column A .
Can anyone let me know why this is happening.
I will give my result set for example :
Date A B C
Jul-10 NULL 2.1672 78.3515
Aug-10 NULL 2.267 79.198
Sep-10 NULL 2.1043 79.3965
Oct-10 NULL 2.3995 78.0285
Nov-10 NULL 2.0211 78.6934
Dec-10 NULL 1.9674 80.0673
Jan-11 NULL 2.1837 78.6394
Feb-11 NULL 1.9476 79.4796
Mar-11 NULL 1.5301 80.4816
Apr-11 NULL 1.4971 79.7671
May-11 NULL 1.2983 80.5105
Jun-11 NULL 1.4446 80.3852
Jul-11 NULL 0.9514 80.5421
Jul-10 19.4813 NULL NULL
Aug-10 18.535 NULL NULL
Sep-10 18.4992 NULL NULL
Oct-10 19.572 NULL NULL
Nov-10 19.2854 NULL NULL
Dec-10 17.9653 NULL NULL
Jan-11 19.1769 NULL NULL
Feb-11 18.5728 NULL NULL
Mar-11 17.9883 NULL NULL
Apr-11 18.7358 NULL NULL
May-11 18.1912 NULL NULL
Jun-11 18.1702 NULL NULL
Jul-11 18.5066 NULL NULL
October 11, 2011 at 11:52 am
Anybody please help !
October 11, 2011 at 12:16 pm
Going to need a bit more to be able to help you understand why you're getting NULLS in Column A.
For starters,the query you're using. If you can turn that into consumable data that would help us too (see the first link in my signature), but we really need to see your code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 11, 2011 at 12:17 pm
I see you are new around here. Welcome. If you could post some ddl, sample data (insert statements) and desired output based on your sample you will likely get some help. It is nearly impossible to tell why your column is null based on the amount of information you posted. Take a look at the first link in my signature for best practices on posting questions in a format that will offer you the best chance of getting a tested solution to your issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 1:05 pm
This following is the data which i am trying to pivot .
I have 39 rows upon pivoting it should return 13 rows but its returning 26 rows.
I am pivoting on Name column which have three values (Medicaid,Medicare ,Commercial) for 13 months.
Data before pivoting :
DateSvcStartYearMonthNameTotalClaimCount
7/1/2010Medicaid2.1672
7/1/2010Medicare78.3515
7/1/2010Commercial19.4813
8/1/2010Medicaid2.267
8/1/2010Medicare79.198
8/1/2010Commercial18.535
9/1/2010Medicaid2.1043
9/1/2010Medicare79.3965
9/1/2010Commercial18.4992
10/1/2010Medicaid2.3995
10/1/2010Medicare78.0285
10/1/2010Commercial19.572
11/1/2010Medicaid2.0211
11/1/2010Medicare78.6934
11/1/2010Commercial19.2854
12/1/2010Medicaid1.9674
12/1/2010Medicare80.0673
12/1/2010Commercial17.9653
1/1/2011Medicaid2.1837
1/1/2011Medicare78.6394
1/1/2011Commercial19.1769
2/1/2011Medicaid1.9476
2/1/2011Medicare79.4796
2/1/2011Commercial18.5728
3/1/2011Medicaid1.5301
3/1/2011Medicare80.4816
3/1/2011Commercial17.9883
4/1/2011Medicaid1.4971
4/1/2011Medicare79.7671
4/1/2011Commercial18.7358
5/1/2011Medicaid1.2983
5/1/2011Medicare80.5105
5/1/2011Commercial18.1912
6/1/2011Medicaid1.4446
6/1/2011Medicare80.3852
6/1/2011Commercial18.1702
7/1/2011Medicaid0.9514
7/1/2011Medicare80.5421
7/1/2011Commercial18.5066
Data after pivoting :
DateABC
Jul-10NULL2.167278.3515
Aug-10NULL2.26779.198
Sep-10NULL2.104379.3965
Oct-10NULL2.399578.0285
Nov-10NULL2.021178.6934
Dec-10NULL1.967480.0673
Jan-11NULL2.183778.6394
Feb-11NULL1.947679.4796
Mar-11NULL1.530180.4816
Apr-11NULL1.497179.7671
May-11NULL1.298380.5105
Jun-11NULL1.444680.3852
Jul-11NULL0.951480.5421
Jul-1019.4813NULLNULL
Aug-1018.535NULLNULL
Sep-1018.4992NULLNULL
Oct-1019.572NULLNULL
Nov-1019.2854NULLNULL
Dec-1017.9653NULLNULL
Jan-1119.1769NULLNULL
Feb-1118.5728NULLNULL
Mar-1117.9883NULLNULL
Apr-1118.7358NULLNULL
May-1118.1912NULLNULL
Jun-1118.1702NULLNULL
Jul-1118.5066NULLNULL
Expected result :
Date CommercialMedicaidMedicare
7/1/201019.48132.167278.3515
8/1/201018.5352.26779.198
9/1/201018.49922.104379.3965
10/1/201019.5722.399578.0285
11/1/201019.28542.021178.6934
12/1/201017.96531.967480.0673
1/1/201119.17692.183778.6394
2/1/201118.57281.947679.4796
3/1/201117.98831.530180.4816
4/1/201118.73581.497179.7671
5/1/201118.19121.298380.5105
6/1/201118.17021.444680.3852
7/1/201118.50660.951480.5421
October 11, 2011 at 1:09 pm
Better but really I don't have time to turn your raw data into actual data. As both Kraig and I asked can you turn this into consumable data (Insert statements), ddl. There is still no table structure posted. Yes I could decipher what the table should look like but that is about all the time I am willing to donate to your cause. I am guessing you would rather I spent my time working on your solution instead of setting up the problem. Remember we don't get paid so help us help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 1:33 pm
DROP TABLE #Test
CREATE Table #test
(
[Date] datetime,Name VARCHAR(50),[Percent] decimal(8,4)
)
INSERT INTO #TEST
SELECT 7/1/2010, 'Medicaid', 2.1672
INSERT INTO #TEST
SELECT 7/1/2010 ,'Medicare' ,78.3515
INSERT INTO #TEST
SELECT 7/1/2010, 'Commercial', 19.4813
INSERT INTO #TEST
SELECT 8/1/2010, 'Medicaid' ,2.267
INSERT INTO #TEST
SELECT 8/1/2010, 'Medicare', 79.198
INSERT INTO #TEST
SELECT 8/1/2010, 'Commercial', 18.535
INSERT INTO #TEST
SELECT 9/1/2010, 'Medicaid', 2.1043
INSERT INTO #TEST
SELECT 9/1/2010, 'Medicare', 79.3965
INSERT INTO #TEST
SELECT 9/1/2010, 'Commercial' ,18.4992
INSERT INTO #TEST
SELECT 10/1/2010 ,'Medicaid', 2.3995
INSERT INTO #TEST
SELECT 10/1/2010 ,'Medicare', 78.0285
INSERT INTO #TEST
SELECT 10/1/2010, 'Commercial', 19.572
INSERT INTO #TEST
SELECT 11/1/2010, 'Medicaid' ,2.0211
INSERT INTO #TEST
SELECT 11/1/2010 ,'Medicare' ,78.6934
INSERT INTO #TEST
SELECT 11/1/2010, 'Commercial', 19.2854
INSERT INTO #TEST
SELECT 12/1/2010 ,'Medicaid', 1.9674
INSERT INTO #TEST
SELECT 12/1/2010, 'Medicare', 80.0673
INSERT INTO #TEST
SELECT 12/1/2010, 'Commercial', 17.9653
INSERT INTO #TEST
SELECT 1/1/2011, 'Medicaid' ,2.1837
INSERT INTO #TEST
SELECT 1/1/2011 ,'Medicare', 78.6394
INSERT INTO #TEST
SELECT 1/1/2011 ,'Commercial' ,19.1769
INSERT INTO #TEST
SELECT 2/1/2011 ,'Medicaid' ,1.9476
INSERT INTO #TEST
SELECT 2/1/2011, 'Medicare' ,79.4796
INSERT INTO #TEST
SELECT 2/1/2011 ,'Commercial', 18.5728
INSERT INTO #TEST
SELECT 3/1/2011, 'Medicaid', 1.5301
INSERT INTO #TEST
SELECT 3/1/2011, 'Medicare' ,80.4816
INSERT INTO #TEST
SELECT 3/1/2011, 'Commercial', 17.9883
INSERT INTO #TEST
SELECT 4/1/2011, 'Medicaid' ,1.4971
INSERT INTO #TEST
SELECT 4/1/2011, 'Medicare' ,79.7671
INSERT INTO #TEST
SELECT 4/1/2011, 'Commercial' ,18.7358
INSERT INTO #TEST
SELECT 5/1/2011, 'Medicaid' ,1.2983
INSERT INTO #TEST
SELECT 5/1/2011, 'Medicare' ,80.5105
INSERT INTO #TEST
SELECT 5/1/2011 ,'Commercial', 18.1912
INSERT INTO #TEST
SELECT 6/1/2011 ,'Medicaid',1.4446
INSERT INTO #TEST
SELECT 6/1/2011, 'Medicare' ,80.3852
INSERT INTO #TEST
SELECT 6/1/2011,'Commercial', 18.1702
INSERT INTO #TEST
SELECT 7/1/2011, 'Medicaid' ,0.9514
INSERT INTO #TEST
SELECT 7/1/2011, 'Medicare' ,80.5421
INSERT INTO #TEST
SELECT 7/1/2011 ,'Commercial' ,18.5066
SELECT * FROM #Test
SELECT [Date] , Commercial,Medicaid,Medicare FROM #Test
PIVOT
(SUM([Percent]) FOR [Name] IN ( Commercial,Medicaid,Medicare )) AS FinalTable
October 11, 2011 at 1:49 pm
Your pivot appears to look fine. Given the data you posted it will return a single row. Your dates are all 1/1/1900 because they aren't wrapped with ''.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 1:58 pm
But when run with my actual data i get two columns for 13 month and again 13 months is repeated for third column .
What do you think the reason can be.
Date A B C
Jul-10 NULL 2.1672 78.3515
Aug-10 NULL 2.267 79.198
Sep-10 NULL 2.1043 79.3965
Oct-10 NULL 2.3995 78.0285
Nov-10 NULL 2.0211 78.6934
Dec-10 NULL 1.9674 80.0673
Jan-11 NULL 2.1837 78.6394
Feb-11 NULL 1.9476 79.4796
Mar-11 NULL 1.5301 80.4816
Apr-11 NULL 1.4971 79.7671
May-11 NULL 1.2983 80.5105
Jun-11 NULL 1.4446 80.3852
Jul-11 NULL 0.9514 80.5421
Jul-10 19.4813 NULL NULL
Aug-10 18.535 NULL NULL
Sep-10 18.4992 NULL NULL
Oct-10 19.572 NULL NULL
Nov-10 19.2854 NULL NULL
Dec-10 17.9653 NULL NULL
Jan-11 19.1769 NULL NULL
Feb-11 18.5728 NULL NULL
Mar-11 17.9883 NULL NULL
Apr-11 18.7358 NULL NULL
May-11 18.1912 NULL NULL
Jun-11 18.1702 NULL NULL
Jul-11 18.5066 NULL NULL
October 11, 2011 at 2:00 pm
I guess the implied thing was that I can't recreate your issue with the data in the format you posted it in. If you want to fix the data I will be happy to take a look for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 2:39 pm
I think this may help in providing you with a tried and tested solution....the code below is based on the data you initially provided.
I have had to rip/strip and create the scripts.....for future reference please take note...this is by far the easiest way to get a qucik answer.....if you dont provide the DDL code and expected results, the majority of people will move on, They are volunteers.
If you are not clear on how to do this, then please post another question.
Hopefully someone now can take your data, paste into SSMS, and provide a solution.
USE [tempdb] --- a safe place
GO
--===== conditionally drop the test table(s)
IF OBJECT_ID('TempDB.dbo.TBL_TEST','U') IS NOT NULL
DROP TABLE TempDB.dbo.TBL_TEST
CREATE TABLE [dbo].[TBL_TEST](
[DateSvcStartYearMonth] [datetime] NULL,
[Name] [varchar](50) NULL,
[TotalClaimCount] [decimal](9, 4) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TBL_TEST]([DateSvcStartYearMonth], [Name], [TotalClaimCount])
SELECT '20100107 00:00:00.000', N'Medicaid', 2.1672 UNION ALL
SELECT '20100107 00:00:00.000', N'Medicare', 78.3515 UNION ALL
SELECT '20100107 00:00:00.000', N'Commercial', 19.4813 UNION ALL
SELECT '20100108 00:00:00.000', N'Medicaid', 2.2670 UNION ALL
SELECT '20100108 00:00:00.000', N'Medicare', 79.1980 UNION ALL
SELECT '20100108 00:00:00.000', N'Commercial', 18.5350 UNION ALL
SELECT '20100109 00:00:00.000', N'Medicaid', 2.1043 UNION ALL
SELECT '20100109 00:00:00.000', N'Medicare', 79.3965 UNION ALL
SELECT '20100109 00:00:00.000', N'Commercial', 18.4992 UNION ALL
SELECT '20100110 00:00:00.000', N'Medicaid', 2.3995 UNION ALL
SELECT '20100110 00:00:00.000', N'Medicare', 78.0285 UNION ALL
SELECT '20100110 00:00:00.000', N'Commercial', 19.5720 UNION ALL
SELECT '20100111 00:00:00.000', N'Medicaid', 2.0211 UNION ALL
SELECT '20100111 00:00:00.000', N'Medicare', 78.6934 UNION ALL
SELECT '20100111 00:00:00.000', N'Commercial', 19.2854 UNION ALL
SELECT '20100112 00:00:00.000', N'Medicaid', 1.9674 UNION ALL
SELECT '20100112 00:00:00.000', N'Medicare', 80.0673 UNION ALL
SELECT '20100112 00:00:00.000', N'Commercial', 17.9653 UNION ALL
SELECT '20110101 00:00:00.000', N'Medicaid', 2.1837 UNION ALL
SELECT '20110101 00:00:00.000', N'Medicare', 78.6394 UNION ALL
SELECT '20110101 00:00:00.000', N'Commercial', 19.1769 UNION ALL
SELECT '20110102 00:00:00.000', N'Medicaid', 1.9476 UNION ALL
SELECT '20110102 00:00:00.000', N'Medicare', 79.4796 UNION ALL
SELECT '20110102 00:00:00.000', N'Commercial', 18.5728 UNION ALL
SELECT '20110103 00:00:00.000', N'Medicaid', 1.5301 UNION ALL
SELECT '20110103 00:00:00.000', N'Medicare', 80.4816 UNION ALL
SELECT '20110103 00:00:00.000', N'Commercial', 17.9883 UNION ALL
SELECT '20110104 00:00:00.000', N'Medicaid', 1.4971 UNION ALL
SELECT '20110104 00:00:00.000', N'Medicare', 79.7671 UNION ALL
SELECT '20110104 00:00:00.000', N'Commercial', 18.7358 UNION ALL
SELECT '20110105 00:00:00.000', N'Medicaid', 1.2983 UNION ALL
SELECT '20110105 00:00:00.000', N'Medicare', 80.5105 UNION ALL
SELECT '20110105 00:00:00.000', N'Commercial', 18.1912 UNION ALL
SELECT '20110106 00:00:00.000', N'Medicaid', 1.4446 UNION ALL
SELECT '20110106 00:00:00.000', N'Medicare', 80.3852 UNION ALL
SELECT '20110106 00:00:00.000', N'Commercial', 18.1702 UNION ALL
SELECT '20110107 00:00:00.000', N'Medicaid', 0.9514 UNION ALL
SELECT '20110107 00:00:00.000', N'Medicare', 80.5421 UNION ALL
SELECT '20110107 00:00:00.000', N'Commercial', 18.5066
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 11, 2011 at 2:43 pm
The most likely reason is that your interim result set has a column that is not being pivoted on nor in the final select list. PIVOT groups by ALL columns not specified in the PIVOT clause even if they're not in the final SELECT statement. The easy solution is to exclude it from the interim result set.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2011 at 2:47 pm
In fact, taking the data that J Livingston posted and modifying the column names to match, your query produced exactly the desired output you were looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 2:50 pm
Thanks for your reply.
I don't know how to put the actual data according to my scenario , but i can say how in general data is repeating for one columns instead of showing with other two columns.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply