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


Pivoting the table


Pivoting the table

Author
Message
Kumar SQL
Kumar SQL
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 176
I need some help in pivoting the table.
The table is like this:
File Main Sub Text
1 1 A. hello
1 1 B. SQL
5 1 A. central
5 1 B. .com

The modified table look like this

File Main A. B.
1 1 hello SQL
5 1 central .com

All text rows related to A come under column A and same B.

i tried a lot with different Pivoting queries but i'm getting either NULLS or empty fields.

Can anyone suggest me how to get this.

Thanks in Advance!
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5402 Visits: 35303
Hi

will you only "ever" have columns A and B to pivot on?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1121 Visits: 2305
If it fixed number of columns classic cross tab should work:



CREATE TABLE #Temp([File] INT, Main INT, Sub CHAR(2), [Text] VARCHAR(20));
INSERT INTO #Temp ([File], Main, Sub, [Text])
VALUES (1, 1, 'A.', 'hello'),
(1, 1, 'B.', 'SQL'),
(5, 1, 'A.', 'central'),
(5, 1, 'B.', '.com');

SELECT
[File]
,Main
,[A.] = MAX(CASE WHEN Sub = 'A.' THEN [Text] END)
,[B.] = MAX(CASE WHEN Sub = 'B.' THEN [Text] END)
FROM #Temp
GROUP BY [File], Main;

DROP TABLE #Temp;



Kumar SQL
Kumar SQL
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 176
Yes, I have only two group of values "A" and "B" need to be pivoted
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5402 Visits: 35303
ashwinrao.k23 (8/10/2012)
Yes, I have only two group of values "A" and "B" need to be pivoted


ok...in which case the solution from rVadim will work for you.

for more info, suggest you search under the "Authors" (left sidebar) for Jeff Moden and his articles on crosstabs and pivots

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Kumar SQL
Kumar SQL
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 176
thanks for the Reply!
If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5402 Visits: 35303
ashwinrao.k23 (8/10/2012)
thanks for the Reply!
If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..


not quite sure what data fields are being "eaten"....maybe if you provide a script of the table....in the format that rVadim set out above, with a representative sample of data that demonstrates your problem (and expected results please)...then I am sure someone will be able to help

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1121 Visits: 2305
Exactly!

If sample you provided doesn't accurately reflect you actual situation then you need to provide a better sample.

People will be more willing to help if you include CREATE TABLE and INSERT statement to generate sample data.
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5402 Visits: 35303
ashwinrao.k23 (8/10/2012)
thanks for the Reply!
If i use Aggregate functions then its eating some of my data fileds. For MAX its giving only maximim valuse of available filds..


hmm...just a thought, but by any chance do you have data like this (based on your original description)

The table is like this:
File Main Sub Text
1 1 A. hello
1 1 B. SQL
5 1 A. central
5 1 B. .com
1 1 A. hello_again
1 1 B. SQL_again

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Kumar SQL
Kumar SQL
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 176
No. all fields will not repeat as same. Text may be same but File and Main will be different..
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