Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to convert vertical data to horizontal Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 4:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:02 AM
Points: 25, Visits: 72

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.
Post #1483636
Posted Tuesday, August 13, 2013 4:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483640
Posted Tuesday, August 13, 2013 7:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1483726
Posted Tuesday, August 13, 2013 7:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
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!

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!
Post #1484048
Posted Friday, August 16, 2013 1:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:02 AM
Points: 25, Visits: 72
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.
Post #1485007
Posted Friday, August 16, 2013 1:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1485011
Posted Friday, August 16, 2013 1:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:02 AM
Points: 25, Visits: 72
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
Post #1485013
Posted Friday, August 16, 2013 2:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1485021
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse