May 6, 2014 at 12:40 pm
WOW!! Thanks Chris. Wonderful Query . Works as expected. Definitely you guyz think about why such requirement. I need to get these format data to my spreadsheet. each of my worksheet on my spreadsheet will have 10000 records. so i will tweak this query to get 10000 records at time by having condition on the select statement.
Thanks a lot for many replies and to all who spent time on this. Good Learning for me.
May 6, 2014 at 1:13 pm
Final Quick Question.
Is it possible to select the rows based on the condition? as i said, i am planning to bringing 10000 records each.
where s.Rn > 10000 and s.Rn < 20001
Where should i enter the above condition on the dynamic sql. I have tried and getting errors mostly. Any suggestions please
May 6, 2014 at 1:25 pm
KGJ-Dev (5/6/2014)
Final Quick Question.Is it possible to select the rows based on the condition? as i said, i am planning to bringing 10000 records each.
where s.Rn > 10000 and s.Rn < 20001
Where should i enter the above condition on the dynamic sql. I have tried and getting errors mostly. Any suggestions please
It would go a long way to providing help if you stated what the errors are.
_______________________________________________________________
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/
May 6, 2014 at 1:26 pm
KGJ-Dev (5/6/2014)
WOW!! Thanks Chris. Wonderful Query . Works as expected. Definitely you guyz think about why such requirement. I need to get these format data to my spreadsheet. each of my worksheet on my spreadsheet will have 10000 records. so i will tweak this query to get 10000 records at time by having condition on the select statement.Thanks a lot for many replies and to all who spent time on this. Good Learning for me.
Still not exactly sure how useful that is. A person can't parse through that many columns and gain anything useful from it.
_______________________________________________________________
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/
May 6, 2014 at 1:41 pm
Hi Sean,
Appreciate your time on this. Here is my try to get the range of data
DECLARE @Statement VARCHAR(MAX)
SET @Statement = '
SELECT *
FROM
(
SELECT
Val,
Cat,
Rn = ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Empid )
FROM (
SELECT
Empid,
Empname,
EmpSalary = CAST(Empsalary as varchar(50)),
Empdesignation = CAST(Empdesignation as varchar(50))
FROM #Sample
)s where Rn > 10000 and Rn < 20001
unpivot (
Val FOR Cat IN (EmpName, EmpSalary, EmpDesignation)
)u
)m
PIVOT(
MAX(Val) FOR Rn IN (' + STUFF(
(SELECT TOP (10000)
[text()] = ',[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(MAX)) + ']'
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
FOR XML PATH(''))
,1,1,'') + ')
)p
'
PRINT @Statement
EXEC(@Statement)
getting the error as
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.
Not sure am i using condition(where Rn > 10000 and Rn < 20001) on the correct place. Any suggestion please
May 6, 2014 at 1:51 pm
Be careful here, all dynamic sql strings must be defined as N' string ', if missing the "N" in front, it will cause an implicit cast from (v)char to n(v)char, effectively limiting the size to 8000 characters, that's 1,125 columns per character:-D
😎
May 6, 2014 at 1:54 pm
Hi Erik/Sean,
Any suggestions how to use Rn to get range of data on the sample please
May 6, 2014 at 2:06 pm
How about select top(x) from y where z > v order by z?
😛
May 6, 2014 at 2:12 pm
Hi Eirik,
It's unclear. Could you please show me the change in my previous query? where do i need to put the condition.
Thakns
May 6, 2014 at 2:13 pm
Sorry for that, will be back with you in a short while.
😎
May 6, 2014 at 5:30 pm
Hi Eirik,
Am waiting for you Gentle Man. Any hope ?
May 6, 2014 at 8:30 pm
For your column always fix,you can use follow code:
declare @sql nvarchar(max)
select @sql=isnull(@sql+',','')+''''+EmpName+'''' from Sample
select @sql = '''EmpName'','+@sql+' union select ''Empsalary'','
select @sql=isnull(@sql,'')+''''+convert(varchar(20),Empsalary)+''',' from Sample
select @sql = stuff(@sql,len(@sql),1,'')+' union select ''EmpDesignation'','
select @sql=isnull(@sql,'')+''''+convert(varchar(60),EmpDesignation)+''',' from Sample
select @sql = stuff(@sql,len(@sql),1,'')
print (@sql)
set @sql='select '+@sql
exec (@sql)
If you want entire dynamic script, you can consider integrate follow code:
SELECT b.name
FROM sysobjects a ,syscolumns b
WHERE a.id = b.id
AND a.type = 'U'
AND a.name = 'Sample'
and b.name!='Empid'
May 6, 2014 at 10:46 pm
KGJ-Dev (5/6/2014)
Hi Eirik,Am waiting for you Gentle Man. Any hope ?
This is a modification to Chris's code, just set the values for start id and record number and off it goes 🙂
😎
IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE N'#Sample%' AND TABLE_SCHEMA = N'dbo')
DROP table #Sample;
Create table #Sample (Empid int primary key identity(1,1),EmpName nvarchar(50),Empsalary nvarchar(50),EmpDesignation nvarchar(50));
insert into #Sample
values(N'Jhon1',N'8000',N'Manager'),
(N'Smith2', N'6000',N'Lead'),
(N'Samuel3',N'4000',N'AccountExecutive'),
(N'Simson4',N'4000',N'AccountSpecialist'),
(N'Eric5', N'22000',N'Director'),
(N'Jonathan6',N'12000',N'SeniorManager') -- 6 rows
INSERT INTO #Sample (EmpName,Empsalary,EmpDesignation)
SELECT EmpName = a.EmpName + CAST(ROW_NUMBER() OVER(ORDER BY f.Empid, e.Empid, d.Empid, c.Empid, b.Empid, a.empid) AS NVARCHAR(5)), a.Empsalary, a.EmpDesignation
FROM #Sample a, #Sample b, #Sample c, #Sample d, #Sample e, #Sample f
-- 46662 rows
DECLARE @COLNUMBER INT = 10;
DECLARE @START_ID INT = 3874;
DECLARE @PARAM_STR NVARCHAR(MAX) = N'@COLNUMBER INT, @START_ID INT';
DECLARE @Statement VARCHAR(MAX) = N''
SELECT @Statement = N'
SELECT *
FROM
(
SELECT
Val,
Cat,
Rn = ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Empid )
FROM (
SELECT TOP (@COLNUMBER)
Empid,
Empname,
EmpSalary,
Empdesignation
FROM #Sample
WHERE Empid BETWEEN @START_ID AND (@START_ID + @COLNUMBER)
)s
unpivot (
Val FOR Cat IN (EmpName, EmpSalary, EmpDesignation)
)u
)m
PIVOT(
MAX(Val) FOR Rn IN (' + STUFF(
(SELECT TOP (@COLNUMBER)
[text()] = N',[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(MAX)) + N']'
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
FOR XML PATH(''))
,1,1,N'') + N')
)p
'
DECLARE @SQL_STR NVARCHAR(MAX) = CAST(@Statement AS NVARCHAR(MAX));
EXEC SP_EXECUTESQL @SQL_STR,@PARAM_STR,@COLNUMBER,@START_ID;
May 7, 2014 at 6:39 am
Awesome Eirik. Perfect sample. Thanks a lot for you and Chris for the wonderful time on this post. Good learning for me.
Cheers!!!!
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply