August 23, 2011 at 2:45 am
Hi Guys,
I have a table called Invoice. It has fields like InvoiceNumber varchar(50), InvoiceDate DateTime etc.
The main table in my database is called File(FileID is the PK)
The Invoice table has FileID as an FK which links to the File Table.
Each file can have 1 or many invoices.
Now I need to write a query that will show all Invoice Numbers as columns.
So say for FileID 1 I have Invoices with Invoice Numbers 001A, 001B, 001C and 001D, in my query 001A throught to 001D must be listed as columns.
Of course there will be row data for each column but I will get to that after I can figure this out. please help. thanks
August 23, 2011 at 2:53 am
That should not be hard to do.
Can you post:
1) Table scripts
2) Sample data
3) Expected results based on the sample data?
If in doubt, see the first article linked in my signature line and see how to post all the above.
-- Gianluca Sartori
August 24, 2011 at 1:26 pm
This article, first of two by Jeff Moden, should help you.
September 2, 2011 at 2:21 am
here goes guys. sorry for the long delay. Please create a Test database and run the script I am posting. Basically, my main table is the File Table. Each file can have multiple invoices (Invoice Table) linked by the FK FileID.
Each File can also have multiple TimeSheets (TimeSheet Table) again linked by FK FileID.
So in the Invoice Table there is a StartDate and InvoiceDate(End Date) column. Each invoice has an InvoiceNumber. the start date and endate define the period the invoice is active.
I will only be retrieving a single file at a time so we can pass through the fileid as a parameter.
So in my resultset each Invoice Number has to be a column. Each field from the Costing Table needs to be a row. Each file will always have to have all costings. On my front end I will have a textbox for each row of every column where the user will enter a value for the corresponding costing.
Here is a bit of a tricky one. Each record from the timesheet table which links to the queried file will also need to be a row. The way the Timesheet will link to the invoice(column) is, if the Effdate of the Timesheet falls in the period between the start date and end date of the invoice, then the Units value must appear in the corresponding column. These records will be grouped by the RateTypeID field. Please chane a few of the EffDate values so that they fall between the Invoice period.
So here is an example of my end result:
the costings per file will get saved to the File_Costings table. That's what the user will enter and save. Then in the query those values must display like so, followed bt the timesheet records:
Costing 001A 001B 001C
Surveyors Fee 100 200 300
Chemists 400 500 600
Lawyers 700 800 900
Timesheet1 100 200 300
very complicated. I really need help please. here is the script:
/****** Object: Table [dbo].[File] Script Date: 07/14/2011 10:04:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[File]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[File](
[FileID] [bigint] IDENTITY(1,1) NOT NULL,
[OpenedDate] [datetime] NULL,
[ClosedDate] [datetime] NULL,
[ReopenedDate] [datetime] NULL,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[File] ON
INSERT [dbo].[File] ([FileID], [OpenedDate], [ClosedDate], [ReopenedDate]) VALUES (69, CAST(0x00009EF000000000 AS DateTime), NULL, NULL)
INSERT [dbo].[File] ([FileID], [OpenedDate], [ClosedDate], [ReopenedDate]) VALUES (70, CAST(0x00009EF000000000 AS DateTime), NULL, NULL)
SET IDENTITY_INSERT [dbo].[File] OFF
/****** Object: Table [dbo].[Costings] Script Date: 07/14/2011 10:04:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Costings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Costings](
[CostingID] [bigint] IDENTITY(1,1) NOT NULL,
[Description] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Active] [bit] NULL,
CONSTRAINT [PK_Costings] PRIMARY KEY CLUSTERED
(
[CostingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[Costings] ON
INSERT [dbo].[Costings] ([CostingID], [Description], [Active]) VALUES (1, N'Surveyor''s Fees', 1)
INSERT [dbo].[Costings] ([CostingID], [Description], [Active]) VALUES (2, N'Chemists', 1)
INSERT [dbo].[Costings] ([CostingID], [Description], [Active]) VALUES (3, N'Lawyers', 1)
SET IDENTITY_INSERT [dbo].[Costings] OFF
/****** Object: Table [dbo].[File_Costings] Script Date: 07/14/2011 10:04:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[File_Costings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[File_Costings](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [bigint] NULL,
[CostingID] [bigint] NULL,
[Value] [decimal](18, 2) NULL,
CONSTRAINT [PK_File_Costings] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
GO
SET IDENTITY_INSERT [dbo].[File_Costings] ON
INSERT [dbo].[File_Costings] ([ID],[FileID], [CostingID], [Value]) VALUES (1, 69, 1, 100.00)
INSERT [dbo].[File_Costings] ([ID],[FileID], [CostingID], [Value]) VALUES (2, 69, 2, 200.00)
INSERT [dbo].[File_Costings] ([ID],[FileID], [CostingID], [Value]) VALUES (3, 69, 3, 300.00)
SET IDENTITY_INSERT [dbo].[File_Costings] OFF
/****** Object: Table [dbo].[TimeSheet] Script Date: 07/14/2011 10:04:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeSheet]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TimeSheet](
[TimeSheetID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [bigint] NULL,
[UserID] [uniqueidentifier] NULL,
[EffDate] [datetime] NULL,
[Units] [decimal](18, 0) NULL,
[RateTypeID] [bigint] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[CreatedDate] [datetime] NULL,
CONSTRAINT [PK_TimeSheet] PRIMARY KEY CLUSTERED
(
[TimeSheetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[TimeSheet] ON
INSERT [dbo].[TimeSheet] ([TimeSheetID], [FileID], [UserID], [EffDate], [Units], [RateTypeID], [CreatedBy], [CreatedDate]) VALUES (45, 69, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EED00000000 AS DateTime), CAST(28 AS Decimal(18, 0)), 3, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EED00000000 AS DateTime))
INSERT [dbo].[TimeSheet] ([TimeSheetID], [FileID], [UserID], [EffDate], [Units], [RateTypeID], [CreatedBy], [CreatedDate]) VALUES (46, 69, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EFD00000000 AS DateTime), CAST(25 AS Decimal(18, 0)), 3, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EED00000000 AS DateTime))
INSERT [dbo].[TimeSheet] ([TimeSheetID], [FileID], [UserID], [EffDate], [Units], [RateTypeID], [CreatedBy], [CreatedDate]) VALUES (47, 69, N'9758029f-7bec-4d77-8e45-f2241c43b654', CAST(0x00009EFD00000000 AS DateTime), CAST(2 AS Decimal(18, 0)), 2, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EED00000000 AS DateTime))
INSERT [dbo].[TimeSheet] ([TimeSheetID], [FileID], [UserID], [EffDate], [Units], [RateTypeID], [CreatedBy], [CreatedDate]) VALUES (48, 69, N'9758029f-7bec-4d77-8e45-f2241c43b654', CAST(0x00009EFD00000000 AS DateTime), CAST(2 AS Decimal(18, 0)), 1, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EED00000000 AS DateTime))
INSERT [dbo].[TimeSheet] ([TimeSheetID], [FileID], [UserID], [EffDate], [Units], [RateTypeID], [CreatedBy], [CreatedDate]) VALUES (49, 69, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EE400000000 AS DateTime), CAST(30 AS Decimal(18, 0)), 1, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009EED00000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[TimeSheet] OFF
GO
/****** Object: Table [dbo].[Invoice] Script Date: 09/02/2011 09:45:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoice]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Invoice](
[InvoiceID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [bigint] NULL,
[InvoiceNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceDate] [datetime] NULL,
[StartDate] [datetime] NULL,
[Amount] [decimal](18, 2) NULL,
[PaidDate] [datetime] NULL,
[PaidAmount] [decimal](18, 2) NULL,
[DebtorID] [bigint] NULL,
[CreditNoteNumber] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED
(
[InvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[Invoice] ON
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [DebtorID], [CreditNoteNumber]) VALUES (27, 69, N'001A', CAST(0x00009F1900000000 AS DateTime), CAST(0x00009EFB00000000 AS DateTime), CAST(11.00 AS Decimal(18, 2)), NULL, NULL, 1, N'CR001')
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [DebtorID], [CreditNoteNumber]) VALUES (28, 70, NULL, CAST(0x00009F1F00000000 AS DateTime), CAST(0x00009F3E00000000 AS DateTime), CAST(90.00 AS Decimal(18, 2)), NULL, NULL, 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [DebtorID], [CreditNoteNumber]) VALUES (29, 69, N'001B', CAST(0x00009F2100000000 AS DateTime), CAST(0x00009F4000000000 AS DateTime), CAST(100.00 AS Decimal(18, 2)), CAST(0x00009F2800000000 AS DateTime), CAST(20.00 AS Decimal(18, 2)), 1, NULL)
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [DebtorID], [CreditNoteNumber]) VALUES (30, 69, N'001D', CAST(0x00009F2000000000 AS DateTime), CAST(0x00009F3F00000000 AS DateTime), CAST(20.00 AS Decimal(18, 2)), NULL, NULL, 1, N'CR002')
INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [DebtorID], [CreditNoteNumber]) VALUES (31, 69, N'001C', CAST(0x00009F4900000000 AS DateTime), CAST(0x00009F6800000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), NULL, NULL, 1, NULL)
SET IDENTITY_INSERT [dbo].[Invoice] OFF
September 7, 2011 at 8:07 am
OK, I'll give it a shot and answer your original question. This should work to build upon your other queries by use of temp tables, JOINs (UNION ALL), or other methods. Anyway, here is one way to get data from rows into columns.
This is using the method(s) from the link provided above. If you really need to do this and have not read the article(s) I would suggest you do so.
/* this example uses a cte (common table expression), you may want to use a temp table, or derived table */
/* create cte to obtain results for cross tab or pivot table */
;WITH columnsToRows (fileid
,invoiceid
,rowNbr /* something to crosstab or pivottable on */
,InvoiceNumber
) AS
(SELECTfileid
,invoiceid
/* get something to use MAX(CASE ... statement on) for cross tab or pivot table on */
/* find unique values in your own tables, used ROW_NUMBER for this example (nothing else existed from example code) */
,ROW_NUMBER() OVER (PARTITION BY fileid ORDER BY fileid, invoiceid)
,InvoiceNumber
FROMinvoice
)
/* only one block of statements can be run at each execution (it's a cte), uncomment each statement to view results */
/* verify results from cte, comment out to execute statement below (in order) */
select * from columnsToRows
/* get the highest row count (in this case four), comment statement above, uncomment below to execute */
--SELECT MAX(rowNbr) FROM columnsToRows
/* use the result from above query to form your cross tab and/or pivot table */
/* cross tab query using above cte, comment out to use pivot table example below */
--SELECTfileid
--,MAX(CASE WHEN rowNbr = 1 then invoicenumber END) AS InvoiceNumber1
--,MAX(CASE WHEN rowNbr = 2 then invoicenumber END) AS InvoiceNumber2
--,MAX(CASE WHEN rowNbr = 3 then invoicenumber END) AS InvoiceNumber3
--,MAX(CASE WHEN rowNbr = 4 then invoicenumber END) AS InvoiceNumber4 -- highest returned value from query above
--
--FROMcolumnsToRows
--
--GROUP BYfileid
/* pivot table method using cte, comment out to use cross tab example above */
--SELECTfileid
--,[1] AS InvoiceNumber1
--,[2] AS InvoiceNumber2
--,[3]
--,[4] -- (four was the highest number return from the MAX statement above)
--
--FROM
--(SELECTfileid
--/* get something to cross tab or pivot table on */
--/* find unique values in your own tables */
--,ROW_NUMBER() OVER (PARTITION BY fileid ORDER BY fileid, invoiceid) AS rowNbr /* represents the [1],[2], etc. above and below */
--,InvoiceNumber
--
--FROMcolumnsToRows
--
--) AS TableToPivot
--PIVOT
--(
--MAX(InvoiceNumber)
--FOR rowNbr IN ([1], [2], [3], [4]) /* add as needed */
--) AS PivotedTable; /* alias name */
September 8, 2011 at 7:23 am
wow. thanks alot for your help:-) I will give it a try and post back.
September 9, 2011 at 1:08 am
hi gregs,
this query is brilliant. almost exactly what I need. The first part is 100%. The second part where it does the Pivoting is where I have a problem.
I need the Invoice Number rows to be Pivoted dynamically in a sense. There could be 20 invoices for one file and 4 for another. I cant do this for example:
PIVOT
(
MAX(InvoiceNumber)
FOR rowNbr IN ([1], [2], [3], [4]) /* add as needed */
) AS PivotedTable; /* alias name */
the /*add as needed*/ part cos I don't know the number of columns each query will return. please advise me;-)
September 9, 2011 at 7:33 am
September 9, 2011 at 8:02 am
You can't put the roof on a house until the walls are up. Start with the foundations - build a query which returns the data you want to process into the pretty result set. Something like this:
SELECT
f.FileID, f.OpenedDate,
fc.Value,
c.[Description], c.Active,
i.InvoiceNumber, i.InvoiceDate, i.StartDate, i.Amount, i.PaidDate, i.PaidAmount, i.CreditNoteNumber,
t.EffDate, t.Units, t.RateTypeID
FROM [dbo].[File] f
LEFT JOIN [dbo].[File_Costings] fc ON fc.FileID = f.FileID
LEFT JOIN [dbo].[Costings] c ON c.CostingID = fc.CostingID
LEFT JOIN [dbo].[Invoice] i ON i.FileID = f.FileID
LEFT JOIN [dbo].[TimeSheet] t ON t.FileID = f.FileID AND t.EffDate BETWEEN i.StartDate AND i.InvoiceDate
Change this to remove columns which are not required in the output, and are not required during processing to derive the output. Put in any simple sums but not aggregates (GROUP BY) at this stage. Explain any aggregation you think you will need.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2011 at 9:36 am
Try this, not too hard to understand. There are many sites with examples of dynamic pivot tables.
/*
put distinct invoice numbers into a comma separated variable
for use in the pivot table function
*/
DECLARE @ColumnHeaders VARCHAR(MAX)
SELECT @ColumnHeaders =
COALESCE(
@ColumnHeaders + ',[' + invoicenumber + ']'
,'[' + invoicenumber + ']'
) FROM
(SELECT DISTINCT invoicenumber -- get distinct invoice numbers
FROM invoice
)aliasName
/* view results from above statement */
--SELECT @ColumnHeaders
/* create dynamic pivot table
it works by using the @ColumnHeaders variable
at the IN clause of the PivotedTable statement
*/
DECLARE @PivotTable NVARCHAR(MAX)
SET @PivotTable = N'
SELECT *
FROM (
SELECTfileid
,InvoiceNumber
FROMinvoice
) AS TableToPivot
PIVOT (
MAX(invoicenumber)
FOR invoicenumber IN (
' + @ColumnHeaders + '
)
) AS PivotedTable
'
EXECUTE(@PivotTable)
September 12, 2011 at 1:42 am
thanks guys. will give this a shot and post back
September 12, 2011 at 6:31 am
By the way, the example sent using Cross Tabs will produce incorrect results. You will need to get a count of invoice id's or invoice numbers, not fileid's.
September 12, 2011 at 7:29 am
thanks again gregs. Your help has thought me so much. I realised I needed to add an InvoiceID field to my file_costings table so I can apply a particular costing to a particular invoice. That would alow me to get the right row,column values in the query. So here is my final stored proc passing in the FileID as a parameter.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE PivotBreakdown
-- Add the parameters for the stored procedure here
@FileID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ColumnHeaders VARCHAR(MAX)
SELECT @ColumnHeaders =
COALESCE(
@ColumnHeaders + ',[' + invoicenumber + ']'
,'[' + invoicenumber + ']'
) FROM
(SELECT DISTINCT invoicenumber -- get distinct invoice numbers
FROM invoice WHERE FileID = @FileID
)aliasName
/* view results from above statement */
--SELECT @ColumnHeaders
DECLARE @PivotTable NVARCHAR(MAX)
SET @PivotTable = N'
SELECT *
FROM (
SELECT c.CostingID, c.Description, i.invoicenumber, fc.value, c.sortorder
FROM Costings C
LEFT JOIN File_Costings FC ON (FC.CostingID = c.CostingID) AND (fc.FileID=' + CONVERT(varchar(MAX),@FileID) + ')
LEFT JOIN Invoice i ON i.invoiceid = fc.invoiceid
WHERE (C.Active = 1)
UNION
SELECT 0 as CostingID, v.RateDescription as [Description], i.invoicenumber, SUM(v.TotalValue) as [value], 99 as sortorder
FROM v_TimeSheets v
inner JOIN Invoice I ON i.fileid = v.fileid and v.EffDate BETWEEN i.StartDate AND i.InvoiceDate
WHERE (v.FileID=' + CONVERT(varchar(MAX),@FileID) + ' )
GROUP BY v.RateDescription, v.RateTypeID, v.FileID , i.invoicenumber
) AS TableToPivot
PIVOT (
SUM(value)
FOR invoicenumber IN (
' + @ColumnHeaders + '
)
) AS PivotedTable
ORDER BY sortorder'
EXECUTE(@PivotTable)
END
GO
the query is super fast. even pulling through the Timesheet details as I needed. I never thought I'd get this far today. you are a legend;-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply