February 16, 2011 at 5:05 am
Attached .xls file with sample data, Please help me with the query. Thanks in advance.
Please see the attachment.
February 16, 2011 at 6:27 am
See the link in my signature on CrossTabs/Pivots. Great explanation of how to accomplish this there.
Also, if you want people on the forums to help you write code, you'll get better results posting sample data in the fashion detailed in another link in my signature "How to Provide Sample Data".
February 16, 2011 at 9:03 pm
This is my interpretation of your puzzle to solve.
1. Create tables and populate data as in your Excel provided.
-- This temp table stores final result
IF OBJECT_ID('tempdb..#CustomerSpread') IS NOT NULL
drop table #CustomerSpread
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerMain]') AND type in (N'U'))
DROP TABLE [dbo].[CustomerMain]
Create Table CustomerMain
(
CustomerID Varchar(10)
,PinCode Varchar(6)
)
INSERT INTO CustomerMain
SELECT 'A12345', '5670'
Union all
SELECT 'D12345', '6570'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
DROP TABLE [dbo].[Customer]
Create Table Customer
(
CustomerID Varchar(10)
,ReferenceID INT
)
INSERT INTO Customer
SELECT 'A12345', 1
Union all
SELECT 'A12345', 1
Union all
SELECT 'D12345', 2
Union all
SELECT 'D12345', 2
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerStatus]') AND type in (N'U'))
DROP TABLE [dbo].[CustomerStatus]
Create Table CustomerStatus
(
CreatedBy Varchar(40)
,ReferenceID INT
,StatusID INT
,UpdatedDate DateTime
)
INSERT INTO CustomerStatus
SELECT 'David',1,2,'2009-07-05'
Union All
SELECT 'Jim',1,1,'2009-07-03'
Union All
SELECT 'David',2,2,'2009-06-10'
Union All
SELECT 'Jim',2,1,'2009-06-08'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerStatusType]') AND type in (N'U'))
DROP TABLE [dbo].[CustomerStatusType]
Create Table CustomerStatusType
(
StatusID INT
,StatusType Varchar(10)
)
INSERT INTO CustomerStatusType
SELECT 1,'Finalised'
Union All
SELECT 2,'Approved'
2. Using the base tables, get the records of your interest.
Select distinct
CM.CustomerID,
C.ReferenceID,
CS.CreatedBy,
CS.UpdatedDate,
CST.StatusType
into #CustomerSpread
From
CustomerMain CM, Customer C, CustomerStatus CS, CustomerStatusType CST
Where
CM.CustomerID = C.CustomerID
and
C.ReferenceID = CS.ReferenceID
and
CS.StatusID = CST.StatusID
SELECT * FROM #CustomerSpread
3. Then arrange columns into one line of data. For your understanding, I called the columns [first],[second],[third],etc...
SELECT CustomerId, ReferenceID
,[first] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',1)
,[second] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',2)
,[third] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',3)
,[forth] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',4)
,[fifth] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',5)
,[sixth] =
Replace([dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',6),',','')
FROM (
SELECT DISTINCT CustomerId, ReferenceID
FROM #CustomerSpread
) J1
CROSS
APPLY (
SELECT StatusType + ' , ' + CreatedBy + ' , ' + Cast(UpdatedDate as Varchar(11)) + ' , '
FROM #CustomerSpread J2
WHERE J2.CustomerId = J1.CustomerId and J2.ReferenceID=J1.ReferenceID
ORDER BY CustomerId ASC
FOR XML PATH (''), TYPE
) Seqs (xml_expr)
ORDER BY
CustomerId, ReferenceID ASC;
4. Finally re-name the column as in your example.
SELECT CustomerId, ReferenceID
--,[first] =
--[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',1)
,[ApprovedBy] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',2)
,[ApprovedDate] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',3)
--,[forth] =
--[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',4)
,[Finalized] =
[dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',5)
,[FinalizedDate] =
Replace([dbo].[fSplitValue](Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)') , ',',6),',','')
FROM (
SELECT DISTINCT CustomerId, ReferenceID
FROM #CustomerSpread
) J1
CROSS
APPLY (
SELECT StatusType + ' , ' + CreatedBy + ' , ' + Cast(UpdatedDate as Varchar(11)) + ' , '
FROM #CustomerSpread J2
WHERE J2.CustomerId = J1.CustomerId and J2.ReferenceID=J1.ReferenceID
ORDER BY CustomerId ASC
FOR XML PATH (''), TYPE
) Seqs (xml_expr)
ORDER BY
CustomerId, ReferenceID ASC;
5. But wait, without this function these scripts would not work. This is split function with delimiter.
CREATE FUNCTION [dbo].[fSplitValue]
(@Text varchar(8000), @Delimiter char(1), @index int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Table table(Code int primary key , strName varchar(8000))
DECLARE @I AS integer
DECLARE @Start AS integer
DECLARE @End AS integer
DECLARE @C AS char(1)
DECLARE @Count AS integer
DECLARE @ReturnValue varchar(8000)
SET @I = 1
SET @Start = 1
SET @End = 0
SET @Count = 1
WHILE @I < len(@Text)
BEGIN
SET @C = substring(@Text, @I, 1)
IF @C = @Delimiter
BEGIN
SET @End = @I
END
IF @End > 0
BEGIN
INSERT INTO @Table values(@Count, substring(@Text, @Start, @I - @Start))
SET @Start = @I + 1
SET @End = 0
SET @Count = @Count + 1
END
SET @I = @I + 1
END
INSERT INTO @Table values(@Count, substring(@Text, @Start, @I - @Start + 1))
SELECT @ReturnValue = strName FROM @Table WHERE CODE = @index
If @ReturnValue IS NULL
BEGIN
SELECT @ReturnValue = strName FROM @Table WHERE CODE = 1
END
RETURN @ReturnValue
END
Could you try and advise see if my interpretation sounds OK.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply