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

Need help on T-SQL. May be Pivot Tables Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 10:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 94, Visits: 490
I've tables and data as following,

GO
/****** Object: Table [dbo].[CV] Script Date: 04/15/2014 00:49:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CV](
[CVID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [PK_CV] PRIMARY KEY CLUSTERED
(
[CVID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (68, N'Ahmad Jaezan Hj Ramly')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (69, N'MUHAMMAD FIRDAUS BIN SHAARI')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (70, N'nadia bt abdul aziz')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (71, N'SITI HAJAR BT MOHAMAD ARIS @ AZIZ')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (72, N'JAMILUDIN BIN JENON')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (76, N'MUHAMAD FERID BIN SARJO')
INSERT [dbo].[CV] ([CVID], [Name]) VALUES (77, N'Lim Jinn An')
/****** Object: Table [dbo].[DeclarationResponse] Script Date: 04/15/2014 00:49:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeclarationResponse](
[DeclarationResponseID] [int] NOT NULL,
[CVID] [int] NOT NULL,
[DeclarationQuestionID] [int] NULL,
[Response] [text] NULL,
[Answer] [bit] NULL,
CONSTRAINT [PK_DeclarationResponse] PRIMARY KEY NONCLUSTERED
(
[DeclarationResponseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [DeclarationResponse_UQ1] UNIQUE NONCLUSTERED
(
[CVID] ASC,
[DeclarationQuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (185, 72, 1, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (187, 72, 3, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (189, 72, 5, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (191, 72, 7, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (193, 77, 1, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (195, 77, 3, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (197, 77, 5, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (199, 77, 7, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (186, 72, 2, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (188, 72, 4, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (190, 72, 6, N'', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (192, 72, 8, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (194, 77, 2, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (196, 77, 4, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (198, 77, 6, N'', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (200, 77, 8, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2025, 76, 1, N'At Ilsas for 5 time', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2027, 76, 3, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2029, 76, 5, N'Technician position', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2031, 76, 7, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2026, 76, 2, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2028, 76, 4, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2030, 76, 6, N'', 1)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (2032, 76, 8, N'', 0)
INSERT [dbo].[DeclarationResponse] ([DeclarationResponseID], [CVID], [DeclarationQuestionID], [Response], [Answer]) VALUES (279741, 76, 9, N'', 0)
/****** Object: ForeignKey [FK_DeclarationResponse_CV] Script Date: 04/15/2014 00:49:42 ******/
ALTER TABLE [dbo].[DeclarationResponse] WITH CHECK ADD CONSTRAINT [FK_DeclarationResponse_CV] FOREIGN KEY([CVID])
REFERENCES [dbo].[CV] ([CVID])
GO
ALTER TABLE [dbo].[DeclarationResponse] CHECK CONSTRAINT [FK_DeclarationResponse_CV]
GO

To built column name and data, see below:-

1- Answer-Staff (9) is a DeclarationQuestionID=9 and Column is Answer

2- Response-Staff (9) is a DeclarationQuestionID=9 and Column is Response

3- Answer-Disease (2) is a DeclarationQuestionID=2 and Column is Answer

4- Response-Physical (2) is a DeclarationQuestionID=2 and Column is Response

The expected result as follow,

CVID    | Answer-Staff (9)    | Response-Staff Number (9)   | Answer-Disease (2)   | Response-Physical (2) 
_____________________________________________________________________________________________________________
72 | null | null | 0 |

76 | 0 | | 0 |

77 | null | null | 0 |

/*null will be given if DeclarationQuestionID not exists*/


I'm stuck. Please help me
Post #1561575
Posted Monday, April 14, 2014 12:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
You did a nice job posting ddl and sample data. However, once you got to the output it went awry very quickly. You have things like "Answer-Staff" , "Response-Staff". What are those? What are the values supposed to be and why? In your sample data you have nothing but empty strings and a couple of actual values for response but in your expected outcome you have NULL, 0. Nowhere in your output are the two actual values from your sample data.

You seem to be missing almost the entirety of the explanation of your desired output.


_______________________________________________________________

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 #1561613
Posted Monday, April 14, 2014 12:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 94, Visits: 490
I knew my table design is silly.

To built column name and data, see below:-

1- Answer-Staff (9) is a DeclarationQuestionID=9 and Column is Answer

2- Response-Staff (9) is a DeclarationQuestionID=9 and Column is Response

3- Answer-Disease (2) is a DeclarationQuestionID=2 and Column is Answer

4- Response-Physical (2) is a DeclarationQuestionID=2 and Column is Response


I've no idea how is SQL looks like
Post #1561622
Posted Monday, April 14, 2014 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
Little Nick (4/14/2014)
I knew my table design is silly.

To built column name and data, see below:-

1- Answer-Staff (9) is a DeclarationQuestionID=9 and Column is Answer

2- Response-Staff (9) is a DeclarationQuestionID=9 and Column is Response

3- Answer-Disease (2) is a DeclarationQuestionID=2 and Column is Answer

4- Response-Physical (2) is a DeclarationQuestionID=2 and Column is Response


I've no idea how is SQL looks like


I agree the design is a bit "left of center"...

Does something like this get you started?

select *
from CV
left join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)

I am still kind of struggling with your expected output.


_______________________________________________________________

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 #1561626
Posted Monday, April 14, 2014 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
OK I think I got it. You are going to need a tally table for this because you have missing data that you want to report.

BTW, you should stop using the text datatype. It is a serious PITA to work with and has been deprecated since 2005. You should use the max datatypes instead.

I still don't really get what you are trying to do here but whatever, I leave the business rules to you.

WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)

select Name,
MAX(case when N = 9 then cast(answer as varchar) end) as AnswerStaff,
MAX(case when N = 9 then cast(Response as varchar(max)) end) as ResponseStaffNumber,
MAX(case when N = 2 then cast(answer as varchar) end) as AnswerDisease,
MAX(case when N = 2 then cast(Response as varchar(max)) end) as ResponsePhysical
from CV
cross apply cteTally t
left join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)
where t.N in (9, 2)
group by Name

There are two different things going on here. First is the tally table. You need to read and understand what that means and how they work. In this case we are using it as a numbers table to drive your query so you get rows with NULL when there is no data present. Read more about tally tables here. http://www.sqlservercentral.com/articles/T-SQL/62867/

The next piece is a bit more complicated. It is using a cross tab query to kludge this data from its poor design into something somewhat more useful.

You can (and should) read about crosstabs by following the links in my signature. I would highly recommend you NOT use this code until you understand what it does and can explain it to someone else. It is your phone that will be ringing at 3am, not mine.


_______________________________________________________________

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 #1561635
Posted Monday, April 14, 2014 12:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 94, Visits: 490
For

select *
from CV
left join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)

This is the result



I only need to display record if DeclarationResponseID is not null. So, the CVID was 72, 76 and 77

See below,
1- I want to make it - the DeclarationQuestionID value is a column.
2- Based on DeclarationQuestionID value, the column will be 2 and 9

Based on CVID=72 and DeclarationQuestionID=2. So, Response='' and Answer=0.
Based on CVID=72, there is no DeclarationQuestionID=9. So, Response=null and Answer=null.


So, the result must be
CVID    | Answer-Staff (9)    | Response-Staff Number (9)   | Answer-Disease (2)   | Response-Physical (2) 
_____________________________________________________________________________________________________________
72 | null | null | 0 |


Based on CVID=76 and DeclarationQuestionID=2. So, Response='' and Answer=0.
Based on CVID=76 and DeclarationQuestionID=9. So, Response='' and Answer=0.

So, the result must be
CVID    | Answer-Staff (9)    | Response-Staff Number (9)   | Answer-Disease (2)   | Response-Physical (2) 
_____________________________________________________________________________________________________________

76 | 0 | | 0 |


Based on CVID=77 and DeclarationQuestionID=2. So, Response='' and Answer=0.
Based on CVID=77, there is no DeclarationQuestionID=9. So, Response=null and Answer=null.

So, the result must be
CVID    | Answer-Staff (9)    | Response-Staff Number (9)   | Answer-Disease (2)   | Response-Physical (2) 
_____________________________________________________________________________________________________________


77 | null | null | 0 |

The value = null must be given, if CVID do not have DeclarationQuestionID=2 nor DeclarationQuestionID=9

Post #1561641
Posted Monday, April 14, 2014 12:53 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
OK so you can drop the tally table portion of my previous post and simplify it to this.

select Name, cv.CVID, 
MAX(case when dr.DeclarationQuestionID = 9 then cast(answer as varchar) end) as AnswerStaff,
MAX(case when dr.DeclarationQuestionID = 9 then cast(Response as varchar(max)) end) as ResponseStaffNumber,
MAX(case when dr.DeclarationQuestionID = 2 then cast(answer as varchar) end) as AnswerDisease,
MAX(case when dr.DeclarationQuestionID = 2 then cast(Response as varchar(max)) end) as ResponsePhysical
from CV
join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)
group by Name, cv.CVID
order by cv.CVID

--edit--

Added CVID to the output.


_______________________________________________________________

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 #1561644
Posted Monday, April 14, 2014 12:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 94, Visits: 490
YIPPIEEEEEEEEE. TQ SIR
Post #1561645
Posted Monday, April 14, 2014 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
Little Nick (4/14/2014)
YIPPIEEEEEEEEE. TQ SIR


BUT...do you understand it? That by far is more important than the fact that it works.


_______________________________________________________________

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 #1561660
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse