Lakshmana S
asked on
MY sql query for converting row value as column header and another column value as sub header
My Table looks like below
host id itemname itemvalue quantity
1 A 10 1
1 B 3 2
2 A 9 3
2 C 40 4
I have attached my output in image , i have tries with procedure but none seems to be worked out .
Capture.PNG
host id itemname itemvalue quantity
1 A 10 1
1 B 3 2
2 A 9 3
2 C 40 4
I have attached my output in image , i have tries with procedure but none seems to be worked out .
Capture.PNG
ASKER
SET @sql = NULL;
set session group_concat_max_len = 10000;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(itemname = ''',
itemname ,
''', itemvalue, NULL)) AS ''',
itemname ,'''' ,
'MAX(IF(itemname = ''',
itemname ,
''', quantity, NULL)) AS ''',
itemname ,''''
)) INTO @sql,@sql2
FROM tbl_main where task=in_task;
SET @sql = CONCAT('SELECT Distinct host_id,', @sql, ' ,',@sql2,' FROM tbl_main WHERE task IN (''',in_task,''') and audited_date between(''',from_date,''') and (''',to_date,''') GROUP BY host_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
I user above procedure for dynamic conversion but resulted in below error
The used SELECT statements have a different number of columns
set session group_concat_max_len = 10000;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(itemname = ''',
itemname ,
''', itemvalue, NULL)) AS ''',
itemname ,'''' ,
'MAX(IF(itemname = ''',
itemname ,
''', quantity, NULL)) AS ''',
itemname ,''''
)) INTO @sql,@sql2
FROM tbl_main where task=in_task;
SET @sql = CONCAT('SELECT Distinct host_id,', @sql, ' ,',@sql2,' FROM tbl_main WHERE task IN (''',in_task,''') and audited_date between(''',from_date,''')
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
I user above procedure for dynamic conversion but resulted in below error
The used SELECT statements have a different number of columns
why you need to use GROUP_CONCAT in your case?
have you tried my suggestion in previous post?
have you tried my suggestion in previous post?
ASKER
I applied the method from your previous post. but i could not use since the data is dynamic I am using group concat since converting row value as column header . kindly share your suggestio.
Try using the following dynamic PIVOT query:
SELECT hostID, RTRIM(itemname) + '_itemvalue' AS [item1], RTRIM(itemname) + '_quantity' As [item2], itemvalue, quantity
INTO #tmpHOST
FROM HOST
DECLARE @cols1 AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX),
@sumcols1 AS NVARCHAR(MAX),
@sumcols2 AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(c.item1)
FROM #tmpHOST c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @cols2 = STUFF((SELECT distinct ',' + QUOTENAME(c.item2)
FROM #tmpHOST c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @sumcols1 = STUFF((SELECT distinct ',SUM(' + QUOTENAME(c.item1) + ') AS ' + QUOTENAME(c.item1)
FROM #tmpHOST c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @sumcols2 = STUFF((SELECT distinct ',SUM(' + QUOTENAME(c.item2) + ') AS ' + QUOTENAME(c.item2)
FROM #tmpHOST c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT hostID, ' + @sumcols1 + ',' + @sumcols2 + ' FROM
( SELECT hostID, RTRIM(itemname) + ''_itemvalue'' AS [item1], RTRIM(itemname) + ''_quantity'' As [item2], itemvalue, quantity
FROM HOST) AS S
PIVOT
(
SUM(itemvalue)
FOR item1 IN (' + @cols1 + ')
) AS pvt1
PIVOT
(
SUM(quantity)
FOR item2 IN (' + @cols2 + ')
) AS pvt2
GROUP BY HostID'
EXECUTE(@query)
DROP TABLE #tmpHOST
@Alfred1
not too sure if stuff and for xml path are working fine since the asker is using MySQL, not MS SQL.
not too sure if stuff and for xml path are working fine since the asker is using MySQL, not MS SQL.
ASKER
Thanks for your efforts alfred . I tried above but got below error .
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[item1], RTRIM(itemname) + '_quantity' As [item2], itemvalue, quantity
INTO FR' at line 1
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[item1], RTRIM(itemname) + '_quantity' As [item2], itemvalue, quantity
INTO FR' at line 1
@Ryan Chong
Thanks for flagging it. I didn't noticed its MySQL. Ok, for author, you could replace STUFF with MySQL INSERT function and adjust accordingly. Or, use GROUP_CONCAT on the ones with STUFF... FOR XML PATH.
Thanks for flagging it. I didn't noticed its MySQL. Ok, for author, you could replace STUFF with MySQL INSERT function and adjust accordingly. Or, use GROUP_CONCAT on the ones with STUFF... FOR XML PATH.
@Author
The query provided unfortunately is for MS SQL and not MySQL as flagged by Ryan Chong. Let me see if I can provide an equivalent one as soon as I can.
The query provided unfortunately is for MS SQL and not MySQL as flagged by Ryan Chong. Let me see if I can provide an equivalent one as soon as I can.
Ok. Try the dynamic query below:
SET @query = null;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = \'',itemname,'\',itemvalue,NULL)) AS itemvalue',itemname))
INTO @maxcols1
FROM HOST;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = \'',itemname,'\',quantity,NULL)) AS quantity',itemname))
INTO @maxcols2
FROM HOST;
SET @query = CONCAT('SELECT hostID, ', @maxcols1,',',@maxcols2 ,
' FROM HOST GROUP BY hostID');
SELECT @query;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ASKER
I tried but again its throughing below error .
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MAX(IF at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MAX(IF at line 1
This might be a maximum length issue. The maximum length for group concat is 1024.
Add the following code above the rest of the code:
SET SESSION group_concat_max_len = 10000;
SET @query = null;
....
......
Add the following code above the rest of the code:
SET SESSION group_concat_max_len = 10000;
SET @query = null;
....
......
ASKER
Hi ,
I am trying another way of achieving the results just concatenating two column.
SET @query = null;
set session group_concat_max_len = 10000;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = ''',itemname ,''',itemvalue,NULL)) AS ''',itemname ,'''') ,CONCAT('MAX(IF(itemname = ''',itemname ,''',quantity,NULL)) AS ''',itemname ,'''' ))
INTO @maxcols1,@maxcols2
FROM tbl_main;
SET @query = CONCAT('SELECT host_id, ', @maxcols1,',',@maxcols2 ,
' FROM tbl_main GROUP BY host_id');
Bur getting below error
#1222 - The used SELECT statements have a different number of columns. can anyone help me overcome this
I am trying another way of achieving the results just concatenating two column.
SET @query = null;
set session group_concat_max_len = 10000;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = ''',itemname ,''',itemvalue,NULL)) AS ''',itemname ,'''') ,CONCAT('MAX(IF(itemname = ''',itemname ,''',quantity,NULL)) AS ''',itemname ,'''' ))
INTO @maxcols1,@maxcols2
FROM tbl_main;
SET @query = CONCAT('SELECT host_id, ', @maxcols1,',',@maxcols2 ,
' FROM tbl_main GROUP BY host_id');
Bur getting below error
#1222 - The used SELECT statements have a different number of columns. can anyone help me overcome this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now its concatenating but the problem if i gave both alias name same it takes only the second @maxcols2 and output is only @maxcols2 .
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = \'',itemname,'\',itemvalue ,NULL)) AS itemvalue',itemname)),
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = \'',itemname,'\',quantity, NULL)) AS quantity',itemname))
INTO @maxcols1,@maxcols2
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = \'',itemname,'\',itemvalue
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(itemname = \'',itemname,'\',quantity,
INTO @maxcols1,@maxcols2
What exactly are you trying to do? If you do a "SELECT @query" on the above code, you get the following query:
SELECT hostID, MAX(IF(itemname = 'A',itemvalue,NULL)) AS itemvalueA,MAX(IF(itemname = 'B',itemvalue,NULL)) AS itemvalueB,MAX(IF(itemname = 'C',itemvalue,NULL)) AS itemvalueC,MAX(IF(itemname = 'A',quantity,NULL)) AS quantityA,MAX(IF(itemname = 'B',quantity,NULL)) AS quantityB,MAX(IF(itemname = 'C',quantity,NULL)) AS quantityC FROM tbl_main GROUP BY hostID
The code below is generated by the first GROUP_CONCAT
MAX(IF(itemname = 'A',itemvalue,NULL)) AS itemvalueA,MAX(IF(itemname = 'B',itemvalue,NULL)) AS itemvalueB,MAX(IF(itemname = 'C',itemvalue,NULL)) AS itemvalueC
The code below is generated by the second GROUP_CONCAT
MAX(IF(itemname = 'A',quantity,NULL)) AS quantityA,MAX(IF(itemname = 'B',quantity,NULL)) AS quantityB,MAX(IF(itemname = 'C',quantity,NULL)) AS quantityC
SELECT hostID, MAX(IF(itemname = 'A',itemvalue,NULL)) AS itemvalueA,MAX(IF(itemname
The code below is generated by the first GROUP_CONCAT
MAX(IF(itemname = 'A',itemvalue,NULL)) AS itemvalueA,MAX(IF(itemname
The code below is generated by the second GROUP_CONCAT
MAX(IF(itemname = 'A',quantity,NULL)) AS quantityA,MAX(IF(itemname = 'B',quantity,NULL)) AS quantityB,MAX(IF(itemname = 'C',quantity,NULL)) AS quantityC
Ok. If you run the MySQL code at ID: 42266000 you will get the following result
hostID itemvalueA itemvalueB itemvalueC quantityA quantityB quantityC
1 10 3 null 1 2 null
2 9 null 40 3 null 4
The only difference is the positioning of the columns and the naming of the columns.
hostID itemvalueA itemvalueB itemvalueC quantityA quantityB quantityC
1 10 3 null 1 2 null
2 9 null 40 3 null 4
The only difference is the positioning of the columns and the naming of the columns.
No response from asker after providing an actual solution to problem.
Open in new window