Click here to Skip to main content
14,871,126 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have this type of data in table.
BR	DL	AP	
 150	21	5


Note: I need data like this

Scode  Total
br      150
dl      21
AP      5


What I have tried:

SQL
WITH CTE
AS
  
  (
  SELECT * FROM (
  SELECT top 1 AP,BR  FROM A) T
  UNPIVOT ( Value FOR N IN (AP,BR))P
  )
 SELECT Value AS Total FROM CTE
Posted
Updated 12-Feb-20 0:55am
v2

1 solution

Please, read the documentation: Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]

Example:
SQL
DECLARE @tmp TABLE(BR INT, DL INT, AP INT)

INSERT INTO @tmp(BR, DL, AP)
VALUES(150, 21, 5)

SELECT Scode, Total
FROM (
	SELECT *
	FROM @tmp
) pvt
UNPIVOT(Total FOR Scode IN (BR, DL, AP)) AS unpvt


Result:
Scode	Total
BR	150
DL	21
AP	5
   
Comments
Er.RajeshKumar 12-Feb-20 7:07am
   
INSERT INTO @tmp(BR, DL, AP)
VALUES(150, 21, 5)

your query is working but value 150,21,5 is in row How can insert in @tmp
Maciej Los 12-Feb-20 7:16am
   
What?
[EDIT]
You are fetching data from table A:
SELECT top 1 AP,BR  FROM A

I'm fetching data from temporary table (stored in variable):
SELECT * FROM @tmp

This is the only different.
All you need to do is to replace @tmp with A (your table name).
MadMyche 12-Feb-20 7:18am
   
Have you reviewed the code and run it? Did you compare the results with what you wanted?
I did, and I run SELECT * FROM @tmp and it seems to be what you asked for
Maciej Los 12-Feb-20 7:24am
   
Thanks!
It helped me to understand what OP is saying...
MadMyche 12-Feb-20 7:41am
   
Welcome you are. try I do
MadMyche 12-Feb-20 7:18am
   
+5
Maciej Los 12-Feb-20 7:20am
   
Thank you.
Er.RajeshKumar 12-Feb-20 8:34am
   
Thanks Its working .
Maciej Los 12-Feb-20 8:36am
   
You're very welcome.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900