Need help in mysql query

Question

1.00/5 (1 vote)

See more:

i have a table in mysql which shows output like this:-

# ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, status_
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, NULL, '3', NULL, NULL
NULL, NULL, NULL, NULL, '4', NULL, NULL
NULL, NULL, NULL, NULL, '2', NULL, NULL
NULL, NULL, NULL, NULL, '1', NULL, NULL
NULL, NULL, NULL, NULL, '5', NULL, NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, NULL, '1'
NULL, NULL, NULL, NULL, NULL, NULL, '1'
NULL, NULL, NULL, NULL, NULL, NULL, '0'
NULL, NULL, NULL, NULL, NULL, NULL, '0'
NULL, NULL, NULL, NULL, NULL, NULL, '0'

but i need output like this:-

# ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, status_
'1', 2, 35, 56, 3, 100, 1
'1', 2, 35, 56, 4, 100, 1
'1', 2, 35, 56, 2, 100, 0
'1', 2, 35, 56, 1, 100, 0
'1', 2, 35, 56, 5, 100, 0

What I have tried:

select  max(ticket_type_id),max(category_id),max(request_type_id),max(item_id),max(department_id),max(created_by),max(status_)
from temp2 tbl  ;

use max but this is not working for me.

Posted 21-Jan-20 18:36pm

TCS54321

Updated 21-Jan-20 20:31pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2020-01-21T20:32:00

Well the data does not seem logical as no relationship is defined between data sub sets. But anyway required result can be acheived by doing some tweeking in the query. Try it

SQL

DECLARE @TBL TABLE
(
	TEMP_ID INT IDENTITY(1, 10),
	ticket_type_id VARCHAR(10),
	category_id VARCHAR(10),
	request_type_id VARCHAR(10),
	item_id VARCHAR(10),
	department_id VARCHAR(10),
	created_by VARCHAR(10),
	[status] VARCHAR(10)
)

INSERT INTO @TBL (ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, [status])
SELECT '1' as ticket_type_id, NULL as category_id, NULL as request_type_id, NULL as item_id, NULL as department_id, NULL as created_by, NULL as status
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '3', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '4', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '2', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '1', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '5', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'

SELECT A.ID, B.category_id, C.request_type_id, D.item_id, E.department_id, F.created_by, G.[status]
FROM
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, ticket_type_id
	from @TBL
	where ticket_type_id is not null
) A
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, category_id
	from @TBL
	where category_id is not null
) B ON A.ID = B.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, request_type_id
	from @TBL
	where request_type_id is not null
) C ON A.ID = C.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, item_id
	from @TBL
	where item_id is not null
) D ON A.ID = D.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, department_id
	from @TBL
	where department_id is not null
) E ON A.ID = E.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, created_by
	from @TBL
	where created_by is not null
) F ON A.ID = F.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, status
	from @TBL
	where status is not null
) G ON A.ID = G.ID

Update!

MySQL version

SQL

CREATE TABLE TEST
(
	TEMP_ID INT NOT NULL AUTO_INCREMENT,
	ticket_type_id VARCHAR(10),
	category_id VARCHAR(10),
	request_type_id VARCHAR(10),
	item_id VARCHAR(10),
	department_id VARCHAR(10),
	created_by VARCHAR(10),
	status VARCHAR(10),
	PRIMARY KEY (TEMP_ID)
);

INSERT INTO TEST (ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, status)
SELECT '1' as ticket_type_id, NULL as category_id, NULL as request_type_id, NULL as item_id, NULL as department_id, NULL as created_by, NULL as status
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '3', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '4', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '2', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '1', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '5', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
;

set @row_number := 0;
set @row_number1 := 0;
set @row_number2 := 0;
set @row_number3 := 0;
set @row_number4 := 0;
set @row_number5 := 0;
set @row_number6 := 0;


SELECT A.ID, B.category_id, C.request_type_id, D.item_id, E.department_id, F.created_by, G.status
FROM
(
	SELECT  (@row_number:=@row_number + 1) as ID, ticket_type_id
	from TEST
	where ticket_type_id is not null
) A
INNER JOIN
(
	SELECT  (@row_number1:=@row_number1 + 1) as ID, category_id
	from TEST
	where category_id is not null
) B ON A.ID = B.ID
INNER JOIN
(
	SELECT  (@row_number2:=@row_number2 + 1) as ID, request_type_id
	from TEST
	where request_type_id is not null
) C ON A.ID = C.ID
INNER JOIN
(
	SELECT  (@row_number3:=@row_number3 + 1) as ID, item_id
	from TEST
	where item_id is not null
) D ON A.ID = D.ID
INNER JOIN
(
	SELECT  (@row_number4:=@row_number4 + 1) as ID, department_id
	from TEST
	where department_id is not null
) E ON A.ID = E.ID
INNER JOIN
(
	SELECT  (@row_number5:=@row_number5 + 1) as ID, created_by
	from TEST
	where created_by is not null
) F ON A.ID = F.ID
INNER JOIN
(
	SELECT  (@row_number6:=@row_number6 + 1) as ID, status
	from TEST
	where status is not null
) G ON A.ID = G.ID