Picking only columns that has values

Transact-SQL
1

Hello all,
I am in need of solution to the following,

I;ve data as below. Original table got nearly 90 columns of such values. Here I just gave 13 columns as example.

ID Name Med1 Med2 Med3 Med4 Med5 Med6 Med7 Med8 Med9 Med10 Med11 Med12 Med13
1 Ramila 1 1
2 Pravena 4 3
3 Vikram 1 4
4 Amra sava 4 4
5 Pintuladuram 3 6
6 Punisurma 1 3 6
7 Meena 1 6 6
8 Reshma 2 4
9 Kana 1 6

Need a query to display the result as below,

ID Name Med Number
1 Ramila Med1 1
1 Ramila Med6 1
2 Pravena Med3 3
2 Pravena Med9 3
3 Vikram Med1 1
3 Vikram Med3 3
4 Amra sava Med3 3
4 Amra sava Med9 3
5 Pintuladuram Med7 3
5 Pintuladuram Med13 6
6 Punisurma Med1 1
6 Punisurma Med4 3
6 Punisurma Med11 6
7 Meena Med1 1
7 Meena Med11 6
7 Meena Med13 6
8 Reshma Med2 2
8 Reshma Med6 4
9 Kana Med1 1
9 Kana Med11 6

Is it possible to derive using MS SQL? If so please help me. - Thank you!

2

You can do this by using UNPIVOT.

A basic sample based on the data you provided:

DROP TABLE  IF EXISTS #Temp;

SELECT
	1 AS ID,
	'Ramila' AS [Name],
	1 As Med1,
	1 AS Med3
INTO #Temp;

SELECT * FROM #Temp;

SELECT
	ID,
	[Name],
	Med
FROM
(
	SELECT
		ID,
		[Name],
		Med1,
		Med3
	FROM
		#temp
) tbl
UNPIVOT
(
	Med FOR [NameOfPerson]
	IN ([Med1],[Med3])
) unpvt;
3 Likes
4 

SELECT ca.*
FROM @T T
CROSS APPLY (
    SELECT T.ID, T.Name, M.Med, M.Number
    FROM (VALUES('Med1', Med1), ('Med2', Med2), ('Med3', Med3), ('Med4', Med4), ('Med5', Med5),
        ('Med6', Med6), ('Med7', Med7), ('Med8', Med8), ('Med9', Med9), ('Med10', Med10), 
        ('Med11', Med11), ('Med12', Med12), ('Med13', Med13)/*, ...*/) AS M(Med,Number)
    WHERE M.Number > 0
) AS ca
ORDER BY T.ID
2 Likes
5

Thank you @ScottPletcher !!
Your query gave me all the needed columns :pray: