February 2, 2017 at 6:25 pm
Hi
I need to do a query that when finds a seq_number with more than one day (day0 to day d62) returns the data in the same row. For example the seq_number 939860789 occurs in 8 days but each data are in a different row and I need all the data of this seq_number stays in the same row of d0/5770 that is the first occurrence.
seq_number d0 d1 d2 d3 d4 d5 d6 d7 d8 d9 d10 d11 d12 d13 d14 d15 d16 d17 d18 d19 d20
939860783 632
939860785 6851
939860789 5770
939860789 V4579
939860789 4019
939860789 2867
939860789 25000
939860789 2722
939860789 42731
939860789 V5861
Thanks
Little
February 2, 2017 at 7:11 pm
I honestly have no idea what you are asking for. But I do know that an easy way to help us help you is to provide a create table statement, some inserts for sample data and your expected output. Then we can write a select statement against real data and validate we provide the correct output.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2017 at 1:56 am
TheSQLGuru - Thursday, February 2, 2017 7:11 PMI honestly have no idea what you are asking for. But I do know that an easy way to help us help you is to provide a create table statement, some inserts for sample data and your expected output. Then we can write a select statement against real data and validate we provide the correct output.
I have to agree with you Kevin - but here's a wild, wild guess:
-- generate a modest set of sample data
IF OBJECT_ID('tempdb..#Sample') IS NOT NULL DROP TABLE #Sample;
CREATE TABLE #Sample (
seq_number CHAR(9),
d0 VARCHAR(6), d1 VARCHAR(6), d2 VARCHAR(6), d3 VARCHAR(6), d4 VARCHAR(6), d5 VARCHAR(6), d6 VARCHAR(6), d7 VARCHAR(6), d8 VARCHAR(6), d9 VARCHAR(6), d10 VARCHAR(6),
d11 VARCHAR(6), d12 VARCHAR(6), d13 VARCHAR(6), d14 VARCHAR(6), d15 VARCHAR(6), d16 VARCHAR(6), d17 VARCHAR(6), d18 VARCHAR(6), d19 VARCHAR(6), d20 VARCHAR(6))
INSERT INTO #Sample (seq_number, d0) VALUES
('939860783', '632'),
('939860785', '6851'),
('939860789', '5770'),
('939860789', 'V4579'),
('939860789', '4019'),
('939860789', '2867'),
('939860789', '25000'),
('939860789', '2722'),
('939860789', '42731'),
('939860789', 'V5861')
-- run a candidate solution against the sample data
SELECT
seq_number,
d0 = MAX(CASE WHEN rn = 0 THEN d0 ELSE NULL END),
d1 = MAX(CASE WHEN rn = 1 THEN d0 ELSE NULL END),
d2 = MAX(CASE WHEN rn = 2 THEN d0 ELSE NULL END),
d3 = MAX(CASE WHEN rn = 3 THEN d0 ELSE NULL END)
FROM (
SELECT seq_number, d0, rn = ROW_NUMBER() OVER(PARTITION BY seq_number ORDER BY (SELECT NULL))-1
FROM #Sample
) d
GROUP BY d.seq_number
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
February 4, 2017 at 4:08 am
This is almost what I need but it shouldn't be necessary to write the values in the query because exists thousands of seq_number.
Thanks
Little
February 4, 2017 at 8:12 am
littlesun - Saturday, February 4, 2017 4:08 AMThis is almost what I need but it shouldn't be necessary to write the values in the query because exists thousands of seq_number.Thanks
Little
"Generate a modest set of sample data" - something to execute the code against.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2017 at 5:15 am
We can go for dynamic Sql, try below solution -- generate a modest set of sample data
IF OBJECT_ID('tempdb..#Sample') IS NOT NULL
DROP TABLE #Sample;
CREATE TABLE #Sample (
seq_number CHAR(9)
,d0 VARCHAR(6)
)
INSERT INTO #Sample (
seq_number
,d0
)
VALUES ('939860783'
,'632'
)
,(
'939860785'
,'6851'
)
,(
'939860789'
,'5770'
)
,(
'939860789'
,'V4579'
)
,(
'939860789'
,'4019'
)
,(
'939860789'
,'2867'
)
,(
'939860789'
,'25000'
)
,(
'939860789'
,'2722'
)
,(
'939860789'
,'42731'
)
,(
'939860789'
,'V5861'
)
-- run below solution against the sample data
DECLARE @var as bigint;
WITH maxSeq AS (
SELECT COUNT(1) cnt
FROM #Sample
GROUP BY seq_number)
SELECT @var = MAX(cnt)
FROM maxSeq
DECLARE @Columns NVARCHAR(4000)
DECLARE @Query NVARCHAR(4000)
DECLARE @i int
SET @i = 0
WHILE @i < @var
BEGIN
SET @Columns = CAST (CASE WHEN @i = 0
THEN '[d' + CAST(@i AS VARCHAR) + ']'
ELSE @Columns + ', [d' + CAST(@i AS VARCHAR) + ']'
END as NVARCHAR(4000))
SET @i = @i +1
END
SELECT @Columns
SET @Query = '
WITH CTE AS
(
SELECT seq_number
,d0 as dat
,rn = ''d''+ CAST(ROW_NUMBER() OVER (
PARTITION BY seq_number ORDER BY (
SELECT NULL
)
) - 1 as VARCHAR(5))
FROM #Sample
)
SELECT *
FROM CTE
PIVOT (
MAX(dat)
for rn in ('+ @Columns + ')
)p';
EXECUTE sp_executesql @statement = @Query
Add or delete records from sample data and see if it gives you results as per your requirement.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply