TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
ahmed elbarbary
NA
1.6k
278k
when Build Dynamic query Error display converting data type varchar to
Jun 22 2020 9:30 PM
I work on SQL server 2012 when Build dynamic Query I get error
Msg 8114, Level 16, State 5, Line 16 Error converting data type varchar to float.
so How to solve this Error ?
I build dynamic query based on @Header and @column and @Body .
@Header represent header must display as Headers .
@column represent pivot columns .
@Body represent select query for data .
create
table
#FinalTable
(
PART_ID nvarchar(50) ,
CompanyName nvarchar(50),
PartNumber nvarchar(50),
DKFeatureName nvarchar(100),
value
float
,
StatusId
int
,
DisplayOrder
int
,
splitFlag
bit
)
insert
into
#FinalTable
values
(
'1222'
,
'Honda'
,
'silicon'
,
'package'
,
'15.50Am'
,2,5,0),
(
'1900'
,
'MERCEIS'
,
'GLASS'
,
'family'
,
'90.00Am'
,2,2,1),
--have column per Unit on @Header because FlagAllow=1
(
'5000'
,
'TOYOTA'
,
'alominia'
,
'source'
,
'70.20kg'
,2,1,0),
(
'8000'
,
'MACDA'
,
'motor'
,
'parametric'
,
'50.40kg'
,2,3,1),
--have column per Unit on @Header because FlagAllow=1
(
'8900'
,
'JEB'
,
'mirror'
,
'noparametric'
,
'75.35kg'
,2,4,0)
DECLARE
@Header NVARCHAR(
MAX
)
SELECT
@Header = STUFF(
(
SELECT
', '
+
case
when
A.splitFlag = 1
and
a.value<>
'-'
and
(a.Value
is
not
null
)
then
''
''
+A.DKFeatureName +
''
' as '
''
+A.DKFeatureName+
''
','
''
+ A.DKFeatureName +
'Units'
+
''
' as '
''
+ A.DKFeatureName +
'Units'
+
''
''
else
''
''
+A.DKFeatureName +
''
' as '
''
+ A.DKFeatureName +
''
''
end
FROM
#FinalTable A
where
StatusId=2
ORDER
BY
DisplayOrder
FOR
XML PATH (
''
)
),1,2,
''
)
DECLARE
@Columns NVARCHAR(
MAX
)
SELECT
@Columns = STUFF(
(
SELECT
', '
+
case
when
A.splitFlag = 1
and
a.value<>
'-'
and
(a.Value
is
not
null
)
then
'['
+A.DKFeatureName+
'],['
+A.DKFeatureName+
'Unit]'
else
quotename(A.DKFeatureName)
end
FROM
#FinalTable A
where
StatusId=2
ORDER
BY
DisplayOrder
FOR
XML PATH (
''
)
),1,2,
''
)
DECLARE
@Body NVARCHAR(
MAX
)
SELECT
@Body = STUFF(
(
SELECT
', '
+
case
when
A.splitFlag = 1
and
a.value<>
'-'
and
(a.Value
is
not
null
)
then
'LEFT('
+ QUOTENAME (A.DKFeatureName) +
',PATINDEX('
'%[^0-9.]%'
','
+ QUOTENAME (A.DKFeatureName) +
'+ '
+
''
' '
''
+
')-1) as ['
+A.DKFeatureName+
'],RIGHT('
+ QUOTENAME (A.DKFeatureName) +
',LEN('
+ QUOTENAME (A.DKFeatureName) +
') - PATINDEX('
'%[^0-9.]%'
','
+ QUOTENAME (A.DKFeatureName) +
')+1) as ['
+A.DKFeatureName +
'Units'
+
']'
else
quotename(A.DKFeatureName)
end
FROM
#FinalTable A
where
StatusId=2
ORDER
BY
A.DisplayOrder
FOR
XML PATH (
''
)
),1,2,
''
)
DECLARE
@SQL NVARCHAR(
MAX
)
select
@SQL =CONCAT('
SELECT
*
Into
#NewTable
FROM
#FinalTable
PIVOT(
max
(Value)
FOR
DKFeatureName
IN
(
'+@Columns+'
))
AS
PVTTable
',
N
' Select '
'PART_ID'
' as '
'PART_ID'
' ,'
'CompanyName'
' as '
'CompanyName'
','
'PartNumber'
' as '
'PartNumber'
' , '
+@Header + '
union
all
select
PART_ID,CompanyName,PartNumber,
' +@Body + '
from
#NewTable
')
EXEC
(@SQL)
Reply
Answers (
2
)
How do you separate a single column in to two columns in SQL?
Top 10 Rows Fetch