Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL query Help

0 views
Skip to first unread message

Peter Newman

unread,
Sep 1, 2010, 3:54:03 AM9/1/10
to

CREATE TABLE [dbo].[FileHeader](
[LedgerKey] [uniqueidentifier] NOT NULL,
[SubmittedDate] [smalldatetime]NOT NULL
CONSTRAINT [FileHeader] PRIMARY KEY NONCLUSTERED
([LedgerKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[FileBody](
[LedgerKey] [uniqueidentifier] NOT NULL,
[TransactionID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]


INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES
('F92B4920-D748-4DF8-9182-E6C524F7840A','585B723C-529E-417B-81DC-F70913A68981')
INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES
('F92B4920-D748-4DF8-9182-E6C524F7840A','17ABABDB-37C8-42D9-8FCA-D7BB37F862B6')
INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES
('F92B4920-D748-4DF8-9182-E6C524F7840A','07BA0D55-05C6-4EA6-A6FE-5E6E99D4AFA7')

INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES
('851AB8E9-4132-4077-897A-2A0357944F73','42CC6935-6F28-495D-9DB6-690A517996A7')

INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES
('39FE65AD-BC73-4F66-BEFC-77F1FD966A0F','ECA088A3-0C4C-441E-BDAF-8DE1D50CE5EC')

INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES
('C4315B0D-8472-4AE8-9CCF-BD22A89E5617','DFCEB78D-23CF-4D24-A690-33E139A85A06')
INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES
('C4315B0D-8472-4AE8-9CCF-BD22A89E5617','DE31811D-E0CB-4076-9205-DF9D83A14D2E')

INSERT INTO [FileHeader] ([LedgerKey] ,[SubmittedDate]) VALUES
('F92B4920-D748-4DF8-9182-E6C524F7840A' , CONVERT(smalldatetime, '03/06/2010'
, 103))
INSERT INTO [FileHeader] ([LedgerKey] ,[SubmittedDate]) VALUES
('851AB8E9-4132-4077-897A-2A0357944F73' , CONVERT(smalldatetime, '09/07/2010'
, 103))
INSERT INTO [FileHeader] ([LedgerKey] ,[SubmittedDate]) VALUES
('39FE65AD-BC73-4F66-BEFC-77F1FD966A0F' ,CONVERT(smalldatetime, '02/03/2010'
, 103))
INSERT INTO [FileHeader] ([LedgerKey] ,[SubmittedDate]) VALUES
('C4315B0D-8472-4AE8-9CCF-BD22A89E5617' ,CONVERT(smalldatetime, '28/08/2010'
, 103))

Result :

Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec
0 0 1 0 0 3 1 2 0 0 0 0

John Bell

unread,
Sep 1, 2010, 5:27:07 PM9/1/10
to


Looks like you want to PIVOT this this is an example using years
http://blogs.msdn.com/b/craigfr/archive/2007/07/03/the-pivot-operator.aspx
also http://msdn.microsoft.com/en-us/library/ms177410.aspx


John

0 new messages