June 15, 2016 at 12:00 pm
Hello, I know I need to use the PIVOT Function, but I don't use it that often and am struggling with it, could someone help me out?
I have a table like:
IDTSBillingIDStepID
13 21
23 22
33 23
43 51
53 52
63 53
711126561
811126572
911416522
1011455783
Which I would like to convert to:
TSBillingIDStep1Step2Step3
3 2 1 2 3
3 5 1 2 3
1112656 1
11126572
11416522
1145578 3
For each step (1,2,& 3) I need the number 1,2,& 3 to appear.
any idea's how I would write this? I'm think I need to find a way to pivot without an aggregation, but not sure.
Thank you!
David92595
June 15, 2016 at 12:10 pm
please read this article and post back some scripts that we can all use easily.
cheers
edit...doh!
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 15, 2016 at 12:35 pm
CREATE TABLE #ATableLike(
ID INTEGER NOT NULL
,TS INTEGER NOT NULL
,BillingID INTEGER NOT NULL
,StepID INTEGER NOT NULL
);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (1,3,2,1);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (2,3,2,2);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (3,3,2,3);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (4,3,5,1);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (5,3,5,2);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (6,3,5,3);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (7,111265,6,1);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (8,111265,7,2);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (9,114165,2,2);
INSERT INTO #ATableLike(ID,TS,BillingID,StepID) VALUES (10,114557,8,3);
SELECT
TS,
BillingId ,
MAX (CASE WHEN StepID = 1 THEN StepID END) Step1,
MAX (CASE WHEN StepID = 2 THEN StepID END) Step2,
MAX (CASE WHEN StepID = 3 THEN StepID END) Step3
FROM #ATableLike
GROUP BY TS,BillingId
ORDER BY TS,BillingId
DROP TABLE #ATableLike
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 15, 2016 at 12:47 pm
Sorry, my spacing\formatting got removed...hope this is a little better.
CREATE TABLE [dbo].[tblOrderBillingSteps](
[OrderBillingStepID] [int] IDENTITY(1,1) NOT NULL,
[lngOrderID] [int] NULL,
[lngBillingStepID] [int] NULL,
[lngSubBillingStepID] [int] NULL,
CONSTRAINT [PK_OrderBillingSteps] PRIMARY KEY CLUSTERED
INSERT INTO tblOrderBillingStepsVALUES (1,3,2,1)
INSERT INTO tblOrderBillingStepsVALUES (2,3,2,2)
INSERT INTO tblOrderBillingStepsVALUES (3,3,2,3)
INSERT INTO tblOrderBillingStepsVALUES (4,3,5,1)
INSERT INTO tblOrderBillingStepsVALUES (5,3,5,2)
INSERT INTO tblOrderBillingStepsVALUES (6,3,5,3)
INSERT INTO tblOrderBillingStepsVALUES (7,4,2,1)
INSERT INTO tblOrderBillingStepsVALUES (8,8,2,1)
INSERT INTO tblOrderBillingStepsVALUES (9,8,2,2)
INSERT INTO tblOrderBillingStepsVALUES (10,10,7,1)
INSERT INTO tblOrderBillingStepsVALUES (11,10,7,3)
OUPUT:
lngOrderID, lngBillingStepID, lngBillingStepID, lngSubBillingStepID =1, lngSubBillingStepID =2, lngSubBillingStepID=3
I would like the lngSubBillingStepID to show their orginal value of 1,2,or 3. Not an aggregation.
June 15, 2016 at 12:57 pm
David92595 (6/15/2016)
Sorry, my spacing\formatting got removed...hope this is a little better.CREATE TABLE [dbo].[tblOrderBillingSteps](
[OrderBillingStepID] [int] IDENTITY(1,1) NOT NULL,
[lngOrderID] [int] NULL,
[lngBillingStepID] [int] NULL,
[lngSubBillingStepID] [int] NULL,
CONSTRAINT [PK_OrderBillingSteps] PRIMARY KEY CLUSTERED
INSERT INTO tblOrderBillingStepsVALUES (1,3,2,1)
INSERT INTO tblOrderBillingStepsVALUES (2,3,2,2)
INSERT INTO tblOrderBillingStepsVALUES (3,3,2,3)
INSERT INTO tblOrderBillingStepsVALUES (4,3,5,1)
INSERT INTO tblOrderBillingStepsVALUES (5,3,5,2)
INSERT INTO tblOrderBillingStepsVALUES (6,3,5,3)
INSERT INTO tblOrderBillingStepsVALUES (7,4,2,1)
INSERT INTO tblOrderBillingStepsVALUES (8,8,2,1)
INSERT INTO tblOrderBillingStepsVALUES (9,8,2,2)
INSERT INTO tblOrderBillingStepsVALUES (10,10,7,1)
INSERT INTO tblOrderBillingStepsVALUES (11,10,7,3)
OUPUT:
lngOrderID, lngBillingStepID, lngBillingStepID, lngSubBillingStepID =1, lngSubBillingStepID =2, lngSubBillingStepID=3
I would like the lngSubBillingStepID to show their orginal value of 1,2,or 3. Not an aggregation.
here's your code tidied up so it works........so please provide expected results.....as an actual table please (so that we dont misinterpret your description )
CREATE TABLE [dbo].[tblOrderBillingSteps](
[OrderBillingStepID] [int] NULL,
[lngOrderID] [int] NULL,
[lngBillingStepID] [int] NULL,
[lngSubBillingStepID] [int] NULL)
INSERT INTO tblOrderBillingSteps VALUES (1,3,2,1)
INSERT INTO tblOrderBillingSteps VALUES (2,3,2,2)
INSERT INTO tblOrderBillingSteps VALUES (3,3,2,3)
INSERT INTO tblOrderBillingSteps VALUES (4,3,5,1)
INSERT INTO tblOrderBillingSteps VALUES (5,3,5,2)
INSERT INTO tblOrderBillingSteps VALUES (6,3,5,3)
INSERT INTO tblOrderBillingSteps VALUES (7,4,2,1)
INSERT INTO tblOrderBillingSteps VALUES (8,8,2,1)
INSERT INTO tblOrderBillingSteps VALUES (9,8,2,2)
INSERT INTO tblOrderBillingSteps VALUES (10,10,7,1)
INSERT INTO tblOrderBillingSteps VALUES (11,10,7,3)
select * from tblOrderBillingSteps
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 15, 2016 at 12:59 pm
THANK YOU!
I've bookmarked that site, thank you
David92595
June 16, 2016 at 1:52 pm
futhre to my previous post.....care to fully explain what you mean by this:
OUPUT:
lngOrderID, lngBillingStepID, lngBillingStepID, lngSubBillingStepID =1, lngSubBillingStepID =2, lngSubBillingStepID=3
I would like the lngSubBillingStepID to show their orginal value of 1,2,or 3. Not an aggregation.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply