Sql Data

Avinash Vajpayee

a1	a2	a3
11	null	null
null	12	null
null	null	13

in this table i want output as follows

Give This Output

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Upendra Pratap Shahi11y ago
Accepted answer
Hi,

Use below query-

SELECT D1 FROM
(
SELECT a1 as D1 FROM dbo.yourtable where a1 IS NOT NULL
UNION ALL
SELECT a2 as D1 FROM dbo.yourtable where a2 IS NOT NULL
UNION ALL
SELECT a3 as D1 FROM dbo.yourtable where a3 IS NOT NULL
) A Where D1 IS NOT NULL
Group by D1
Upendra Pratap Shahi11y ago
Thanks for appreciation Avinash Vajpayee
Sabyasachi Mishra11y ago
Try this

SELECT D1 FROM
(
SELECT a1 as D1 FROM dbo.yourtable where a1 IS NOT NULL
UNION ALL
SELECT a2 as D1 FROM dbo.yourtable where a2 IS NOT NULL
UNION ALL
SELECT a3 as D1 FROM dbo.yourtable where a3 IS NOT NULL
) A Where D1 IS NOT NULL
Group by D1

----------------------------------------
Hope that helps.
Please accept all the answers that helps you.
Sabyasachi Mishra11y ago
Can you please share screen shoot of this with your query, because I have tested this with data provided by you.

----------------------------------------
Hope that helps.
Please accept all the answers that helps you.
Avinash Vajpayee11y ago
its Working Fine But It Has 4 Rows In Which Last Row Contain Null Why
Avinash Vajpayee11y ago
ok I will Check It thanks
Sabyasachi Mishra11y ago
SELECT D1 FROM
(
SELECT a1 as D1 FROM dbo.yourtable where a1 IS NOT NULL
UNION ALL
SELECT a2 as D1 FROM dbo.yourtable where a2 IS NOT NULL
UNION ALL
SELECT a3 as D1 FROM dbo.yourtable where a3 IS NOT NULL
) A
Group by D1

Here I am expecting D1 as dummy column name you have given and a1,a2,a3 as your column names of your SQL table

----------------------------------------
Hope that helps.
Khan Abrar Ahmed11y ago
Can you please explain little bit more, what is D1.