August 13, 2013 at 12:24 pm
I have the following result from my SQL query:
EventID P_NumPN_NameCount1PN_Name
ABC-I-109426831089213 1 Company 1
ABC-I-109426831326624 8 Company 2
ABC-I-10942683 1565423 1 Compnay 3
ABC-I-10942683 1659874 2 Company 4
I am still learning this capability in SQL and need some assistance, Pivot's are NOT working in this scenario.
I have tried several different ways in attempting to do this, but was not able to create the desired results:
EventID P_Num1PNC1PN_NamePNC_Num2 PNC2PN_Name
ABC-I-1094268310892131Company 11326624 8Company 2
The `EventID` will change based on the different events from the companies, as the `EventID` is based on a particular date the event occurred with the company.
This is just a sample of the 500K+ rows of data I am working with. This will go into a temp table to be joined with the other various pieces of data needed.
I have tried this without success:
SELECT Key,
MAX(Col1) AS Col1,
MAX(Col2) AS Col2,
MAX(Col3) AS Col3
FROM table
GROUP BY Key
August 13, 2013 at 1:08 pm
Hi and welcome to the forums. What you are describing is known as a cross tab. It will generally outperform a pivot. The challenge is that you posted some data and output but they just don't match up very well. It is considered best practice around here to post readily consumable ddl and sample data. I cobbled together something from what you posted as an example of how to post data and an example of how to achieve your desired output.
with Something(EventID, PNum, NameCount, Name, SomeOtherColumn) as
(
select 'ABC-I-10942683', 1089213, 1, 'Company', 1 union all
select 'ABC-I-10942683', 1326624, 8, 'Company', 2 union all
select 'ABC-I-10942683', 1565423, 1, 'Compnay', 3 union all
select 'ABC-I-10942683', 1659874, 2, 'Company', 4
)
select EventID, MAX(case when SomeOtherColumn = 1 then PNum end) as PNum1,
MAX(case when SomeOtherColumn = 2 then PNum end) as PNum2,
MAX(case when SomeOtherColumn = 3 then PNum end) as PNum3,
MAX(case when SomeOtherColumn = 4 then PNum end) as PNum4
from Something
group by EventID
_______________________________________________________________
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/
August 13, 2013 at 1:23 pm
Thank you for the welcome...For the I-##### in the code, can I replace this with the column name, etc.?
i.e. select eventID, P_Num, 1, 'Company', 1 union all
EventId will be different for every interaction with any company from a differnet timestamp.
August 13, 2013 at 1:27 pm
Also thank you for the reading material on the posting rules...
August 13, 2013 at 1:33 pm
GTJohnson73 (8/13/2013)
Thank you for the welcome...For the I-##### in the code, can I replace this with the column name, etc.?i.e. select eventID, P_Num, 1, 'Company', 1 union all
EventId will be different for every interaction with any company from a differnet timestamp.
Absolutely you can. I only used a cte because I don't have any base tables to use as a basis for my query. You can just pull your data directly from whatever table you need and skip the cte part.
_______________________________________________________________
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/
August 13, 2013 at 2:22 pm
Here is my attemp at the Cross Tab and I am getting invalid column with the underline being on all of the Selects in the "WITH" section. I am doing something incorrectly? The first select under the Declare statement is the actual pull from the DB...this will turn into a Temp tbl in the near future. Included the entire code this time...Thanks...
Declare
@BeginDate DateTime
,@EndDate DateTime
Set @BeginDate ='2013-07-01'
Set @EndDate = '2013-07-31'
--------------------------------------------------------------------------------------------
SELECT
PCW1.pyEventID
,PCW1.ProviderName
,PCW1.ProviderNumber
,Count(PCW1.ProviderName) AS Cnt_Provider
FROM Database_c PCW1
WHERE PCW1.pxCreateDateTime>=@BeginDate AND PCW1.pxCreateDateTime<=@EndDate
AND PCW1.pyEventID Like ('%I-%')
AND PCW1.ProviderNumber IS NOT NULL
AND PCW1.ProviderName IS NOT NULL
GROUP BY PCW1.pyEventID
,PCW1.ProviderNumber
,PCW1.ProviderName
ORDER BY PCW1.pyEventID ASC
----------------------------------------------------------------------------------------------
WITH PCW1(pyEventID,ProviderName,ProviderNumber,Provider_c) AS
(
Select pyEventID,ProviderName,ProviderNumber, 1 union all
Select pyEventID,ProviderName,ProviderNumber, 2 union all
Select pyEventID,ProviderName,ProviderNumber, 3 union all
Select pyEventID,ProviderName,ProviderNumber, 4
)
SELECT pyEventID
,MAX (CASE WHEN Provider_c = 1 THEN ProviderName END) AS P_NUM1
,MAX (CASE WHEN Provider_c = 2 THEN ProviderName END) AS P_NUM2
,MAX (CASE WHEN Provider_c = 3 THEN ProviderName END) AS P_NUM3
,MAX (CASE WHEN Provider_c = 4 THEN ProviderName END) AS P_NUM4
FROM PCW1
GROUP BY pyEventID
August 13, 2013 at 2:27 pm
GTJohnson73 (8/13/2013)
Here is my attemp at the Cross Tab and I am getting invalid column with the unline being on all of the Selects in the "WITH" section. I am doing something incorrectly? Thanks...
Declare@BeginDate DateTime
,@EndDate DateTime
Set @BeginDate ='2013-07-01'
Set @EndDate = '2013-07-31'
--------------------------------------------------------------------------------------------
SELECT
PCW1.pyEventID
,PCW1.ProviderName
,PCW1.ProviderNumber
,Count(PCW1.ProviderName) AS Cnt_Provider
FROM Database_c PCW1
WHERE PCW1.pxCreateDateTime>=@BeginDate AND PCW1.pxCreateDateTime<=@EndDate
AND PCW1.pyEventID Like ('%I-%')
AND PCW1.ProviderNumber IS NOT NULL
AND PCW1.ProviderName IS NOT NULL
GROUP BY PCW1.pyEventID
,PCW1.ProviderNumber
,PCW1.ProviderName
ORDER BY PCW1.pyEventID ASC
----------------------------------------------------------------------------------------------
WITH PCW1(pyEventID,ProviderName,ProviderNumber,Provider_c) AS
(
Select pyEventID,ProviderName,ProviderNumber, 1 union all
Select pyEventID,ProviderName,ProviderNumber, 2 union all
Select pyEventID,ProviderName,ProviderNumber, 3 union all
Select pyEventID,ProviderName,ProviderNumber, 4
)
SELECT pyEventID
,MAX (CASE WHEN Provider_c = 1 THEN ProviderName END) AS P_NUM1
,MAX (CASE WHEN Provider_c = 2 THEN ProviderName END) AS P_NUM2
,MAX (CASE WHEN Provider_c = 3 THEN ProviderName END) AS P_NUM3
,MAX (CASE WHEN Provider_c = 4 THEN ProviderName END) AS P_NUM4
FROM PCW1
GROUP BY pyEventID
I think you have yourself confused here. Your cte has no table and the values are not hardcoded like I did. You don't need a cte at all. I had to because I didn't have a table so I used a cte to simulate a table. Also, when using a cte it is required that the previous statement be terminated with a semicolon.
I can help you with your query but you will need to post your table ddl and a few rows of sample data.
_______________________________________________________________
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/
August 13, 2013 at 2:58 pm
I am working off of a view, that has a huge amount of columns...for the data I need here is the structure information
pyEventID = (Varchar (255), null)
ProviderName = (varchar(64), null)
ProviderNumber = (varchar(10),null)
Here is the sample data I am trying to place in the individual columns...the unfiltered data set has over 200K rows of data. With the single row result, it will reduce the output tremendously. Thank you for the assistance.
EventID PNum Name NameCount
ABC-I-10942683 1089213 Company 1 1
ABC-I-10942684 1326624 Company 2 1
ABC-I-10942685 1099347 Company 3 12
ABC-I-10942686 1303867 Company 4 1
ABC-I-10942687 10027239 Company 5 1
ABC-I-10942688 1255608 Company 6 5
ABC-I-10942689 10019170 Company 7 12
ABC-I-10942690 10017844 Company 8 1
ABC-I-10942691 10006409 Company 9 1
ABC-I-10942692 1178255 Company 10 5
ABC-I-10942693 432782 Company 11 1
ABC-I-10942694 10066360 Company 12 1
ABC-I-10942695 10029259 Company 13 7
August 13, 2013 at 3:06 pm
GTJohnson73 (8/13/2013)
I am working off of a view, that has a huge amount of columns...for the data I need here is the structure informationpyEventID = (Varchar (255), null)
ProviderName = (varchar(64), null)
ProviderNumber = (varchar(10),null)
Here is the sample data I am trying to place in the individual columns...the unfiltered data set has over 200K rows of data. With the single row result, it will reduce the output tremendously. Thank you for the assistance.
EventID PNum Name NameCount
ABC-I-10942683 1089213 Company 1 1
ABC-I-10942684 1326624 Company 2 1
ABC-I-10942685 1099347 Company 3 12
ABC-I-10942686 1303867 Company 4 1
ABC-I-10942687 10027239 Company 5 1
ABC-I-10942688 1255608 Company 6 5
ABC-I-10942689 10019170 Company 7 12
ABC-I-10942690 10017844 Company 8 1
ABC-I-10942691 10006409 Company 9 1
ABC-I-10942692 1178255 Company 10 5
ABC-I-10942693 432782 Company 11 1
ABC-I-10942694 10066360 Company 12 1
ABC-I-10942695 10029259 Company 13 7
Can you turn into a readily consumable format? Create table statement followed by an insert statement. That is what I will have to do. Once again you have mentioned columns and provided data but they don't match up.
In your "columns" you have:
pyEventID
ProviderName
ProviderNumber
But in your output you have:
EventID
PNum
Name
NameCount
From this description it sounds like you don't want a crosstab at all you just need to aggregate your data.
select
pyEventID,
ProviderName,
ProviderNumber,
count(*) as NameCount
from WhateverYourTableNameIs
group by pyEventID, ProviderName, ProviderNumber
_______________________________________________________________
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/
August 13, 2013 at 3:20 pm
My apologies for any confusion, again this area of SQL is new to me and I feel as if my brain is short circuiting...I actually do need a cross tab where the Name of the various companies is in its own column, along with their number in its own column. I have already created a count for the number of times each company has had an "interaction" in the database based on the pyEventID.
The code I sent in the last post was the actual code with the actual names of the columns I am using, therefore causing the difference in column names...
I am creating the other information that you requested...
August 13, 2013 at 3:28 pm
GTJohnson73 (8/13/2013)
My apologies for any confusion, again this area of SQL is new to me and I feel as if my brain is short circuiting...I actually do need a cross tab where the Name of the various companies is in its own column, along with their number in its own column. I have already created a count for the number of times each company has had an "interaction" in the database based on the pyEventID.The code I sent in the last post was the actual code with the actual names of the columns I am using, therefore causing the difference in column names...
I am creating the other information that you requested...
OK sounds good. I probably can't get back to this until tomorrow. I will try to check in this evening after the kids go to bed but no promises. 🙂
_______________________________________________________________
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/
August 13, 2013 at 3:48 pm
Here is the additional information that you requested....This is of a more realistic input with having two different EventIDs with both having multiple "interactions" with different Companies. Let me know if there is anything else I need. Again...thank you for the assistance.
CREATE TABLE #Provider_Info_TEST
(
pyEventID Varchar(255),
ProviderName varchar(64),
ProviderNumber varchar(10)
);
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-10942683','Company 1','432782');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-10942683','Company 2','1089213');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-10942683','Company 3','1099347');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-10942683','Company 4','1178255');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-10942683','Company 5','1255608');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-10942683','Company 6','1303867');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-23942546','Company A','1326624');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-23942546','Company B','10006409');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-23942546','Company C','10017844');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-23942546','Company D','10019170');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-23942546','Company E','10027239');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-23942546','Company F','10029259');
INSERT INTO #Provider_Info_TEST(pyEventID,ProviderName,ProviderNumber)
VALUES ('ABC-I-23942546','Company G','10066360');
Select * FROM #Provider_Info_TEST
August 13, 2013 at 3:55 pm
No worries...I am going home myself to be with the kids and family...will check back on tomorrow...have a great night.
August 14, 2013 at 9:26 am
OK so now if I understand your requirements you are wanting a result set with a dynamic number of columns?
Something like pyEventID, ProviderName1, ProviderNumber1 [repeat Name and Number for however many there are]
grouped by pyEventID???
_______________________________________________________________
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/
August 14, 2013 at 9:34 am
Yes. That is exactly what is needed.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply