SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to convert vertical data to horizontal


how to convert vertical data to horizontal

Author
Message
venkidesaik
venkidesaik
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 73
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 1 2
PQR 1 2
XYZ 1 2

Please give the solution.
Your Help will be appreciable
It helps me alot.

Thanks In Advance,
Venki Desai.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61183 Visits: 13297
Take a look at the PIVOT function.

Using PIVOT and UNPIVOT


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61273 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17413 Visits: 6431
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. :-P


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
venkidesaik
venkidesaik
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 73
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
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61183 Visits: 13297
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
venkidesaik
venkidesaik
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 73
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
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61183 Visits: 13297
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).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search