Link to home
Start Free TrialLog in
Avatar of Lakshmana S
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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try:

select
hostid,
max(if(itemname = 'A', itemvalue, null)) itemval_A,
max(if(itemname = 'A', quantity, null)) quantity_A,
max(if(itemname = 'B', itemvalue, null)) itemval_B,
max(if(itemname = 'B', quantity, null)) quantity_B,
max(if(itemname = 'C', itemvalue, null)) itemval_C,
max(if(itemname = 'C', quantity, null)) quantity_C
from yourtable
group by hostid

Open in new window

Avatar of Lakshmana S
Lakshmana S

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
why you need to use GROUP_CONCAT in your case?

have you tried my suggestion in previous post?
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

Open in new window

@Alfred1

not too sure if stuff and for xml path are working fine since the asker is using MySQL, not MS SQL.
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
@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.
@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.
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;

Open in new window

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
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;
....
......
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
ASKER CERTIFIED SOLUTION
Avatar of Alfred A.
Alfred A.
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Hi ,

This is what i am exactly trying to do  IN MYSQL

User generated image
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.
No response from asker after providing an actual solution to  problem.