how to convert vertical data to horizontal

Question

Post reply

how to convert vertical data to horizontal

venkidesaik

Right there with Babe

Points: 785
More actions
August 13, 2013 at 4:26 am

#297697

Hi All,
CREATE TABLE TBL_SAMPLE
(
Name nVarchar(5),
Unit nVarchar(3),
Figure Int
)
INSERT INTO TBL_SAMPLE VALUES('ABC','m',1)
INSERT INTO TBL_SAMPLE VALUES('PQR','m',1)
INSERT INTO TBL_SAMPLE VALUES('XYZ','m',1)
INSERT INTO TBL_SAMPLE VALUES('ABC','ft',2)
INSERT INTO TBL_SAMPLE VALUES('PQR','ft',2)
INSERT INTO TBL_SAMPLE VALUES('XYZ','ft',2)
SELECT * FROM TBL_SAMPLE
OUTPUT:
Name Unit Figure
------------------------------
ABC m 1
PQR m 1
XYZ m 1
ABC ft 2
PQR ft 2
XYZ ft 2
How to get output like below according to above records.
Name m ft
---------------------
ABC 12
PQR 12
XYZ 12
Please give the solution.
Your Help will be appreciable
It helps me alot.
Thanks In Advance,
Venki Desai.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Take a look at the PIVOT function.

Using PIVOT and UNPIVOT

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Thanks for posting ddl and readily consumable data!!! I wish more people would post like that.

PIVOT can cause some performance issues so I generally try to avoid it. Instead I prefer to use a cross tab. You can read more about cross tabs by following the links in my signature.

This produces the results as you stated:

SELECT Name,

MIN(case when Unit = 'm' then Figure end) as m,

MIN(case when Unit = 'ft' then Figure end) as ft

FROM TBL_SAMPLE

group by name

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

If all you're doing is converting feet to meters (or vice versa), why do you even need to UNPIVOT?

I believe there are conversion factors for that. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

venkidesaik Right there with Babe Points: 785 More actions · Answer 4

Thanks for replying,

I had another doubt

After Running the below Select Statement what you given for example my records are like below,

SELECT Name,

MIN(case when Unit = 'tt' then ISNULL(Figure,0) end) as tt,

MIN(case when Unit = 'm' then ISNULL(Figure,0) end) as m,

MIN(case when Unit = 'ft' then ISNULL(Figure,0) end) as ft

FROM TBL_SAMPLE

group by name

Name tt m ft

---------------------------------------

ABC 20 10 NULL

PQR 30 90 NULL

XYZ 60 15 NULL

Now my requirement is if ft = NULL then i want to do ( m/tt ) so now the total records i want to display is,

Name tt m ft

---------------------------------------

ABC 20 10 0.5

PQR 30 90 3

XYZ 60 15 0.25

so this is my final result

how to resolve this.

Thanks in Advance.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Just add another CASE statement where you check if the expression is NULL with IS NULL. If it is, use the expression m / tt. You might want to put this in an outer query for better readability.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

venkidesaik Right there with Babe Points: 785 More actions · Answer 6

Thanks for replying

i wrote like this but it is still getting null

MIN(case when Unit = '% wt.' then ISNULL(Figure,0) when ISNULL(Figure,0)=NULL then Mwt/vol end) as wt

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

I said use IS NULL to compare.

For example:

CASE WHEN myColumn IS NULL THEN do something ELSE do something else END

You cannot compare columns directly with NULL (myColumn = NULL will always return false).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP