April 9, 2014 at 1:35 pm
Hi
i have the following table of data (sample of one below in script)
I need to convert the last column into seperate rows along with some sort of original order/postion flag.
the field i'm interested in splitting is the last one. (eg: '3211111111111111+'
see below for the create & insert script to see the sample table.
The results i would really like to see with from the sample daat is also scripted below.
Many Thanks In Advance, Really stuck on this one !!!
CREATE AND INSERT SAMPLE VALUE
CREATE TABLE [dbo].[Luke_PS2](
[Ward or Team Name] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[Setting] [nvarchar](255) NULL,
[Service] [nvarchar](255) NULL,
[Updated] [datetime] NULL,
[Count] [float] NULL,
[Audit 1] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Luke_PS] ([Ward or Team Name], [Date], [Setting],[Service],[Updated], [Count],[Audit 1] )
VALUES ('ABC Health Care', '2012-07-11', 'Community', 'DISTRICT NURSING', '2012-01-01', '26', '3211111111111111+')
EXPECTED RESULTS
Ward or Team NameDateSettingServiceUpdatedCountAudit 1"Original
_Position"
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122631
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122622
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122613
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122614
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122615
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122616
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122617
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122618
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/20122619
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226110
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226111
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226112
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226113
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226114
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226115
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226116
ABC Health Care11/07/2012CommunityDISTRICT NURSING18/07/201226+17
Thanks in advance for any advice.
April 9, 2014 at 1:47 pm
This is exactly the kind of thing where a tally table is perfect. I have a view on my system for a tally table but in this code I included how you can use a cte type of tally table.
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
Tally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select *,
SUBSTRING([Audit 1], N, 1)
from [Luke_PS2]
cross apply (select N from Tally where N <= LEN([Audit 1])) x
You should read further about tally tables here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
_______________________________________________________________
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/
April 9, 2014 at 2:00 pm
Try this. Please note that the script also drops the table when completed to keep my sand box database clean.
CREATE TABLE [dbo].[Luke_PS2](
[Ward or Team Name] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[Setting] [nvarchar](255) NULL,
[Service] [nvarchar](255) NULL,
[Updated] [datetime] NULL,
[Count] [float] NULL,
[Audit 1] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Luke_PS2] ([Ward or Team Name], [Date], [Setting],[Service],[Updated], [Count],[Audit 1] )
VALUES ('ABC Health Care', '2012-07-11', 'Community', 'DISTRICT NURSING', '2012-01-01', '26', '3211111111111111+')
with e1(n) as (select n from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select a.n from e1 a cross join e1 b),
etally(n) as (select row_number() over (order by (select null)) n from e2 a cross join e2 b)
select
l.[Ward or Team Name],
l.[Date],
l.[Setting],
l.[Service],
l.[Updated],
l.[Count],
ca.[Audit 1],
ca.n
from
dbo.Luke_PS2 l
cross apply (select top (len(l.[Audit 1])) substring(l.[Audit 1],n,1), n from etally)ca([Audit 1],n);
go
drop table dbo.Luke_PS2;
go
April 9, 2014 at 2:05 pm
Well, looks like Sean and I have similar but different solutions.
April 9, 2014 at 2:18 pm
Lynn Pettis (4/9/2014)
Well, looks like Sean and I have similar but different solutions.
Yes indeed. One suggestion I would offer is to include an order by for your subquery to ensure you always get the same top rows. 😉
_______________________________________________________________
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/
April 9, 2014 at 3:02 pm
Sean Lange (4/9/2014)
Lynn Pettis (4/9/2014)
Well, looks like Sean and I have similar but different solutions.Yes indeed. One suggestion I would offer is to include an order by for your subquery to ensure you always get the same top rows. 😉
Are you sure? Couldn't we say the same about yours? 😉
April 10, 2014 at 2:59 am
Thanks for both of yor replies, Works a treat.
I have landed at work this morining to be presented with the full picture.
same table layout but apparently we have 40 Audit columns. Arrgghh !!!
would it also be possible to tweak your scripts to reflect the revisions below.
I have only show Audit 1, Audit 2, and Audit 3 but we have 40 columns.
I also need to add a flag in the results so that Audit number is shown on each row.
Thanks in advance
New Build script and expect results below.
CREATE AND INSERT TEST VALUES
CREATE TABLE [dbo].[Luke_PS2](
[Ward or Team Name] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[Setting] [nvarchar](255) NULL,
[Service] [nvarchar](255) NULL,
[Updated] [datetime] NULL,
[Count] [float] NULL,
[Audit 1] [nvarchar](255) NULL,
[Audit 2] [nvarchar](255) NULL,
[Audit 3] [nvarchar](255) NULL,
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Luke_PS2] ([Ward or Team Name], [Date], [Setting],[Service],[Updated], [Count],[Audit 1],[Audit 2],[Audit 3])
VALUES ('ABC Health Care', '2012-07-11', 'Community', 'DISTRICT NURSING', '2012-01-01', '26',
'3211111111111111+',
'1234567891011121+',
'9998887776665554+'
)
select * from [dbo].[Luke_PS2]
drop table dbo.Luke_PS2;
EXPECTED RESULTS
[Ward or Team Name], [Date], [Settin], [Service], [Updated], [Count], [Audit_No], [Score], [Original_Position]
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 3, 1,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 2, 2,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 3,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 4,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 5,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 6,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 7,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 8,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 9,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 10,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 11,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 12,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 13,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 14,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 15,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, 1, 16,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 1, +, 17,
-----------------------------------------------------------------------------------------
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 1, 1,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 2, 2,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 3, 3,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 4, 4,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 5, 5,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 6, 6,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 7, 7,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 8, 8,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 9, 9,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 1, 10,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 0, 11,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 1, 12,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 1, 13,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 1, 14,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 2, 15,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, 1, 16,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 2, +, 17,
-----------------------------------------------------------------------------------------
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 9, 1,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 9, 2,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 9, 3,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 8, 4,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 8, 5,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 8, 6,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 7, 7,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 7, 8,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 7, 9,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 6, 10,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 6, 11,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 6, 12,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 5, 13,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 5, 14,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 5, 15,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, 4, 16,
ABC Health Care, 11/07/2012, Community, DISTRICT NURSING, 18/07/2012, 26, Audit 3, +, 17,
April 10, 2014 at 3:40 am
All you need to is to unpivot your table 1st.
then use the logic which been shared earlier.
To unpivot you can do this
;With cLuke_PS2
AS
(
SELECT [Ward or Team Name], [Date], [Setting],[Service],[Updated], [Count]
, Audit_No =ROW_NUMBER() OVER (ORDER BY [Ward or Team Name])
, [toBeAudit]
FROM [Luke_PS2]
CROSS APPLY (
VALUES ([Audit 1])
,([Audit 2])
,([Audit 3])) x ([toBeAudit])
)
Select *
FROM cLuke_PS2
For the set you can use the above logic.
Hope it helps
April 10, 2014 at 12:53 pm
To incorporate the code from twin.devil, this is what I wrote:
CREATE TABLE [dbo].[Luke_PS2](
[Ward or Team Name] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[Setting] [nvarchar](255) NULL,
[Service] [nvarchar](255) NULL,
[Updated] [datetime] NULL,
[Count] [float] NULL,
[Audit 1] [nvarchar](255) NULL,
[Audit 2] [nvarchar](255) NULL,
[Audit 3] [nvarchar](255) NULL,
) ON [PRIMARY];
GO
INSERT INTO [dbo].[Luke_PS2] ([Ward or Team Name], [Date], [Setting],[Service],[Updated], [Count],[Audit 1],[Audit 2],[Audit 3])
VALUES ('ABC Health Care', '2012-07-11', 'Community', 'DISTRICT NURSING', '2012-01-01', '26',
'3211111111111111+',
'1234567891011121+',
'9998887776665554+'
);
with e1(n) as (select n from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select a.n from e1 a cross join e1 b),
etally(n) as (select row_number() over (order by (select null)) n from e2 a cross join e2 b),
cLuke_PS2 AS (
SELECT
[Ward or Team Name],
[Date],
[Setting],
[Service],
[Updated],
[Count],
Audit_No = ROW_NUMBER() OVER (ORDER BY [Ward or Team Name]),
[toBeAudit]
FROM
[Luke_PS2]
CROSS APPLY (
VALUES
([Audit 1]),
([Audit 2]),
([Audit 3])) x ([toBeAudit])
)
Select
l.[Ward or Team Name],
l.[Date],
l.[Setting],
l.[Service],
l.[Updated],
l.[Count],
l.Audit_No,
ca1.[Audit 1],
ca1.n
FROM
cLuke_PS2 l
cross apply (select top (len(l.[toBeAudit])) substring(l.[toBeAudit],n,1), n from etally)ca1([Audit 1],n);
go
drop table dbo.Luke_PS2;
go
April 11, 2014 at 12:19 am
nice Lynn 🙂
April 11, 2014 at 3:42 am
Many Thanks for everyone's help and advice. All sorted now, thanks to your advice.
Regards
April 14, 2014 at 7:02 am
Lynn Pettis (4/9/2014)
Sean Lange (4/9/2014)
Lynn Pettis (4/9/2014)
Well, looks like Sean and I have similar but different solutions.Yes indeed. One suggestion I would offer is to include an order by for your subquery to ensure you always get the same top rows. 😉
Are you sure? Couldn't we say the same about yours? 😉
I was saying that your subquery selects top X from your cte but there is no order by so it is conceivably possible that you could get values from your tally greater than len(Audit).
Neither of us ordered the actual results. 😛
_______________________________________________________________
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/
April 14, 2014 at 7:45 am
Sean Lange (4/14/2014)
Lynn Pettis (4/9/2014)
Sean Lange (4/9/2014)
Lynn Pettis (4/9/2014)
Well, looks like Sean and I have similar but different solutions.Yes indeed. One suggestion I would offer is to include an order by for your subquery to ensure you always get the same top rows. 😉
Are you sure? Couldn't we say the same about yours? 😉
I was saying that your subquery selects top X from your cte but there is no order by so it is conceivably possible that you could get values from your tally greater than len(Audit).
Neither of us ordered the actual results. 😛
Technically you are correct, we are relying on the ORDER BY in the ROW_NUMBER() OVER function to always return the data in the proper order. Seems to me that many of us rely on that when using the dynamic eTally in our solutions and have probably been luck that it always seems to work.
April 14, 2014 at 7:57 am
Lynn Pettis (4/14/2014)
Sean Lange (4/14/2014)
Lynn Pettis (4/9/2014)
Sean Lange (4/9/2014)
Lynn Pettis (4/9/2014)
Well, looks like Sean and I have similar but different solutions.Yes indeed. One suggestion I would offer is to include an order by for your subquery to ensure you always get the same top rows. 😉
Are you sure? Couldn't we say the same about yours? 😉
I was saying that your subquery selects top X from your cte but there is no order by so it is conceivably possible that you could get values from your tally greater than len(Audit).
Neither of us ordered the actual results. 😛
Technically you are correct, we are relying on the ORDER BY in the ROW_NUMBER() OVER function to always return the data in the proper order. Seems to me that many of us rely on that when using the dynamic eTally in our solutions and have probably been luck that it always seems to work.
Most likely with only 1,000 rows it should work. I just am probably hyper-sensitive because I got burned a number of years ago by assuming the order would be the clustered index. Since then I have been a bit OCD about the order by when using top.
_______________________________________________________________
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/
April 14, 2014 at 8:11 am
Sean Lange (4/14/2014)
Lynn Pettis (4/14/2014)
Sean Lange (4/14/2014)
Lynn Pettis (4/9/2014)
Sean Lange (4/9/2014)
Lynn Pettis (4/9/2014)
Well, looks like Sean and I have similar but different solutions.Yes indeed. One suggestion I would offer is to include an order by for your subquery to ensure you always get the same top rows. 😉
Are you sure? Couldn't we say the same about yours? 😉
I was saying that your subquery selects top X from your cte but there is no order by so it is conceivably possible that you could get values from your tally greater than len(Audit).
Neither of us ordered the actual results. 😛
Technically you are correct, we are relying on the ORDER BY in the ROW_NUMBER() OVER function to always return the data in the proper order. Seems to me that many of us rely on that when using the dynamic eTally in our solutions and have probably been luck that it always seems to work.
Most likely with only 1,000 rows it should work. I just am probably hyper-sensitive because I got burned a number of years ago by assuming the order would be the clustered index. Since then I have been a bit OCD about the order by when using top.
Well, like I said, we both should probably have used an ORDER BY in our subqueries up above. You may not have had a TOP in yours but still no guarantee that your would return in the proper order any more than mine. Agreed?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply