Pivoting based on bit column

Question

Post reply

Pivoting based on bit column

gaurav-404321

SSCommitted

Points: 1593
More actions
April 5, 2010 at 5:23 am

#213476

I have three column
Questionid int
type varchar
checked bit
data is getting stored in format like:
1Cont. 0
1Supervisor1
1Site Engg.0
1DQA 1
1SC 0
1CE/CA0
2Cont. 0
2Supervisor0
2Site Engg.0
2DQA 0
2SC 0
2CE/CA0
i want to pivot the results to get the information in following format
questionid [cont] [supervisor] [siteengg] [doa] [sc] [ce/ca]
1 0 1 0 1 0 0
2 0 0 0 0 0 0
I dont know how to pivot based on bit column . can anyone help me plz

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

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

DECLARE @Sample

TABLE (

Questionid INTEGER NOT NULL,

type VARCHAR(30) NOT NULL,

checked BIT

);

INSERT @Sample

(Questionid, type, checked)

SELECT 1, 'Cont.', 0 UNION ALL

SELECT 1, 'Supervisor', 1 UNION ALL

SELECT 1, 'Site Engg.', 0 UNION ALL

SELECT 1, 'DQA', 1 UNION ALL

SELECT 1, 'SC', 0 UNION ALL

SELECT 1, 'CE/CA', 0 UNION ALL

SELECT 2, 'Cont.', 0 UNION ALL

SELECT 2, 'Supervisor', 0 UNION ALL

SELECT 2, 'Site Engg.', 0 UNION ALL

SELECT 2, 'DQA', 0 UNION ALL

SELECT 2, 'SC', 0 UNION ALL

SELECT 2, 'CE/CA', 0;

SELECT P.*

FROM (

SELECT Questionid, type, checked = CONVERT(INTEGER, checked)

FROM @Sample

) S

PIVOT (

MAX(checked)

FOR type IN ([cont.], [supervisor], [site engg.], [dqa], [sc], [ce/ca])

) P;

Next time, please post the CREATE TABLE and INSERT statements yourself, thanks 🙂

I also corrected the errors in your sample data 😛

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 2

gaurav-404321

SSCommitted

Points: 1593

April 5, 2010 at 5:44 am

#1145638

Thanks a ton budy.

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 3

I have one more column in table as remarks.

DECLARE @Sample

TABLE (

Questionid INTEGER NOT NULL,

type VARCHAR(30) NOT NULL,

checked BIT,

remarks varchar(30)

);

for every type there is remarks

Please advise how can i modify the query to include remarks for every type per questionid.

Either it will be a different row for remarks of every type or it will show in columns

Paul White SSC Guru Points: 150468 More actions · Answer 4

Post some sample INSERT statements to populate your table with sample data, and show the exact output you expect.

Thanks.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 5

DECLARE @Sample

TABLE (

Questionid INTEGER NOT NULL,

type VARCHAR(30) NOT NULL,

checked BIT,

Remarks VARCHAR(250)

);

INSERT @Sample

(Questionid, type, checked, Remarks)

SELECT 1, 'Cont.', 0,’Good’ UNION ALL

SELECT 1, 'Supervisor', 1,'Average' UNION ALL

SELECT 1, 'Site Engg.', 0, 'Good' UNION ALL

SELECT 1, 'DQA', 1, 'Good' UNION ALL

SELECT 1, 'SC', 0, 'Good' UNION ALL

SELECT 1, 'CE/CA', 0, 'Good' UNION ALL

SELECT 2, 'Cont.', 0, 'Good' UNION ALL

SELECT 2, 'Supervisor', 0,'' UNION ALL

SELECT 2, 'Site Engg.', 0,'' UNION ALL

SELECT 2, 'DQA', 0, 'Average' UNION ALL

SELECT 2, 'SC', 0, 'Abcdsf' UNION ALL

SELECT 2, 'CE/CA', 0, 'Good';

OutPut Something like:

Questionidcont.supervisorsite engg.dqascce/caRemarksCont.RemarksSupervisorRemarksSiteEnRemarksdqaRemarksscRemarksce

1001100goodAverage

2011000

3111000good

4000000Average

5000000good

6000000

7111001Averagegood

8011000

9000000AveragegoodAverage

10111111

Paul White SSC Guru Points: 150468 More actions · Answer 6

SELECT SQ.Questionid,

cont = MAX(CASE WHEN type = 'Cont.' THEN checked ELSE 0 END),

sup = MAX(CASE WHEN type = 'Supervisor.' THEN checked ELSE 0 END),

se = MAX(CASE WHEN type = 'Site Engg.' THEN checked ELSE 0 END),

dqa = MAX(CASE WHEN type = 'DQA' THEN checked ELSE 0 END),

sc = MAX(CASE WHEN type = 'SC' THEN checked ELSE 0 END),

ceca = MAX(CASE WHEN type = 'CE/CA' THEN checked ELSE 0 END),

c_rem = MAX(CASE WHEN type = 'Cont.' THEN Value ELSE SPACE(0) END),

sup_rem = MAX(CASE WHEN type = 'Supervisor.' THEN Value ELSE SPACE(0) END),

se_rem = MAX(CASE WHEN type = 'Site Engg.' THEN Value ELSE SPACE(0) END),

dqa_rem = MAX(CASE WHEN type = 'DQA' THEN Value ELSE SPACE(0) END),

sc_rem = MAX(CASE WHEN type = 'SC' THEN Value ELSE SPACE(0) END),

ceca_rem = MAX(CASE WHEN type = 'CE/CA' THEN Value ELSE SPACE(0) END)

FROM (

SELECT U.Questionid,

U.type,

U.checked,

Remarks = U.type + ' ' + Name,

U.Value

FROM (

SELECT Questionid, type, checked = CONVERT(INTEGER, checked), Remarks

FROM @Sample

) S

UNPIVOT (

Value FOR Name IN (Remarks)

) U

) SQ

GROUP BY

SQ.Questionid

ORDER BY

SQ.Questionid;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gatekeeper SSCommitted Points: 1633 More actions · Answer 7

Are these user-defined or static values? It looks like each insert is going to produce 6 records for each QuestionID. Have you thought about expanding the table to handle each of those types? Seems like it would be better to have a table with the required columns rather than needing to write (UN)PIVOT queries.

/* Anything is possible but is it worth it? */

Paul White SSC Guru Points: 150468 More actions · Answer 8

Gatekeeper (4/5/2010)
Seems like it would be better to have a table with the required columns rather than needing to write (UN)PIVOT queries.

Agreed - there are definitely opportunities to improve the table design 😉

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi